PostgreSQL for IoT

Big Data Management for IoT

PostgreSQL can be used to store data in IoT. I have followed the following methods to convert the time (UNIT time to dd-mm-yy) format, store the data in postgresql and then to print the data in the table.

1. As the first step, I have installed TimescaleDB database in my computer.
2. Then, I have created a database with name '\IoTdata'.
3. After that, I have created a hypertable with a name '\powerdata' within '\IoTdata'.
4. Then I have converted UNIX time format to proper date-time format using python and saved it in another csv file name 'Time.csv'.
5. After that I have inserted the new converted csv file in the powerdata and printed all the 4000 rows.
6. In the last step, I have printed first and last five rows of the powerdata.

Microsoft Windows [Version 10.0.19042.928]
(c) Microsoft Corporation. All rights reserved.

C:\Users\supri>psql -U postgres -h localhost
Password for user postgres:
psql (11.11)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.


postgres=# create database iotdata;
CREATE DATABASE

postgres=# \c iotdata
You are now connected to database "iotdata" as user "postgres".

iotdata=# create extension if not exists timescaledb;
WARNING:
WELCOME TO

 _____ _                               _     ____________
|_   _(_)                             | |    |  _  \ ___ \
  | |  _ _ __ ___   ___  ___  ___ __ _| | ___| | | | |_/ /
  | | | |  _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \
  | | | | | | | | |  __/\__ \ (_| (_| | |  __/ |/ /| |_/ /
  |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/

           Running version 2.1.1</br>

For more information on TimescaleDB, please visit the following links:

1. Getting started: https://docs.timescale.com/getting-started
   
2. API reference documentation: https://docs.timescale.com/api
   
3. How TimescaleDB is designed: https://docs.timescale.com/introduction/architecture
   

Note: TimescaleDB collects anonymous reports to better understand and assist our users.
For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry.

CREATE EXTENSION

iotdata=# create table powerdata(time TIMESTAMPTZ NOT NULL, power DOUBLE PRECISION NULL, current TEXT NULL, voltage DOUBLE PRECISION NULL, frequency DOUBLE PRECISION NULL, power_factor DOUBLE PRECISION NULL);
CREATE TABLE

iotdata=# select create_hypertable('powerdata','time');
create_hypertable ------------------------ (1,public,powerdata,t) (1 row)
iotdata=# \copy powerdata from 'C:\Users\supri\Downloads\IoT_Data.csv' DELIMITER ',' csv header;
COPY 4000

iotdata=# select * from powerdata fetch first 5 row only;

      time                   |    power    | current |  voltage  | frequency | power_factor
      -----------------------+-------------+---------+-----------+-----------+--------------
      2013-09-08 18:30:00-05 | 28368.60645 | NA      | 240.99646 |  50.32322 |      0.93397
      2013-09-08 18:31:00-05 | 28411.98828 | NA      | 241.19509 |  50.35931 |      0.93536
      2013-09-08 18:32:00-05 | 28752.53809 | NA      | 241.38574 |  50.39784 |      0.93675
      2013-09-08 18:33:00-05 | 29031.44629 | NA      | 241.72626 |  50.44616 |      0.93685
      2013-09-08 18:34:00-05 | 28487.45801 | NA      | 241.94244 |  50.44468 |      0.93448

(5 rows)

iotdata=#

iotdata=# select * from powerdata order by time desc limit 5;

      time                   |    power    | current |  voltage  | frequency | power_factor
      -----------------------+-------------+---------+-----------+-----------+--------------
      2013-12-08 13:09:00-06 | 48596.77344 | NA      | 239.12647 |  50.02576 |      0.95946
      2013-12-08 13:08:00-06 | 46434.46875 | NA      | 239.44028 |  50.06322 |      0.95489
      2013-12-08 13:07:00-06 | 48676.02344 | NA      | 239.73373 |  50.14679 |      0.95049
      2013-12-08 13:06:00-06 |  46271.4707 | NA      | 240.04881 |  50.22065 |      0.95461
      2013-12-08 13:05:00-06 | 46893.51562 | NA      | 240.30111 |  50.28455 |      0.95621

(5 rows)