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.
- As the first step, I have installed TimescaleDB database in my computer.
- Then, I have created a database with name '\IoTdata'.
- After that, I have created a hypertable with a name '\powerdata' within '\IoTdata'.
- Then I have converted UNIX time format to proper date-time format using python and saved it in another csv file name 'Time.csv'.
- After that I have inserted the new converted csv file in the powerdata and printed all the 4000 rows.
- 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:
- Getting started: https://docs.timescale.com/getting-started
- API reference documentation: https://docs.timescale.com/api
- 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)