Insert local time in db

General
1

I have created the following db with ;

CREATE TABLE hvac2 ( id INTEGER PRIMARY KEY AUTOINCREMENT, CURRENT_TIMESTAMP , hp NUMERIC, lp  NUMERIC, cltmp NUMERIC,evaptmp NUMERIC,cmpI NUMERIC,  device TEXT)

Just for test I'm trying to INSERT some values into a db including local time (PST).


INSERT INTO hvac2 ( CURRENT_TIMESTAMP ,hp,lp,device) values( DateTime('now') ,779,800, "Meson2");

What I got is something like that when PST is 8:38 pm:

id       CURRENT_TIMESTAMP       hp     lp
12	2020-01-06 04:32:56	779	800	
13	2020-01-06 04:32:57	779	800	
14	2020-01-06 04:32:58	779	800	
15	2020-01-06 04:32:59	779	800	
16	2020-01-06 04:32:59	779	800	
17	2020-01-06 04:33:00	779	800	
18	2020-01-06 04:33:01	779	800	
19	2020-01-06 04:33:02	779	800	
20	2020-01-06 04:33:02	779	800	
21	2020-01-06 04:33:03	779	800

So what I'm getting is UTC time!!!

How I should modify to get PST ?

Thank you very much !

2

Date and times are not an easy subject.

What kind of database are you using ?
It could be solved in either javascript or in your database. Some databases have extensive support for timezone conversions.

3

It is almost always a bad idea to put local time into a database. For example, over a DST change you may have multiple records with the same timestamp. Put UTC in the database and convert it for display if necessary when you fetch it. Grafana, for example, requires timestamps in UTC.

1 Like
4

As @Colin wrote leave UTC on your database.
You can use getTimezoneOffset() of the Date objects to get the local time offset in minutes.

5

I agree, always store date/times as UTC, never as local. It makes things much easier in the long run.

When you need to convert to local for output, you can use the moment node which lets you convert to any timezone and any format of output.

2 Likes
6

@TotallyInformation @Colin @bakman2 @Christian-Me

Thank you very much for your suggestion I gonna give a try. !!!!!

1 Like
7

Hi @TotallyInformation !

I followed your suggestion about use moment node but because I have not excellent background on JS and I'm new at node -red I would need your valuable help.

The following is a pic of the flow which inject info into SQLlite node, afterward I'm trying to include in output message from SQLlite node my local time, here is where Moment node is inserted.

Flow for HVAC
Flow for HVAC1178Ă—536 35.8 KB

The following is the debug tab showing the Sqlite node output and Moment node output.


1/14/2020, 7:27:27 PMnode: d055066.600d4f8
msg : string[77]
"The input property was NOT a recognisable date. Output will be a blank string"
1/14/2020, 7:27:27 PMnode: Otput Sqlite Node
SELECT * FROM hvac : msg : Object
object
_msgid: "84e88944.b16598"
topic: "SELECT * FROM hvac"
payload: array[12]
[0 … 9]
0: object
id: 1361
currentdate: "2020-01-15"
currenttime: "02:51:03"
hp: 129
lp: 567
cltmp: null
evaptmp: null
cmpI: null
device: "Meson2"
1: object
2: object
3: object
4: object
5: object
6: object
7: object
8: object
9: object
[10 … 11]
1/14/2020, 7:27:27 PMnode: Output Moment Node
SELECT * FROM hvac : msg : Object
object
_msgid: "84e88944.b16598"
topic: "SELECT * FROM hvac"
payload: ""

Hope you can help me.
Thanks in advance for your time.

8

Are you storing the date and time separately in the database? Best not to do that, just save the complete timestamp and split it up later if you need to. If you want to save the current time when you write a record you can tell the database that the timestamp column should automatically be set to the current time when you write a record so you don't need to do anything else.

1 Like
9

Yes, I'm saving the complete UTC timestamp in the database.!
My goal is use the dashboard to select a range of local date-time
then convert it to UTC timestamp to query the database. Afterward I intend to shows
that query on a HTML table in the dashboard.
As I explained you above, maybe I can use the MOMENT node to convert UTC timestamp to local with this node but I don't know how to do it.
What I really want to achieve is include every row coming from the database in the Moment node payload along with local time and show it all in a HTML table.
Hope you can Help me.
Thank you.

10

As indicated above, you can solve this either in node-red or in the query itself.

If you do a query like:

mariadb:

SELECT TIME(CONVERT_TZ(CURRENT_TIMESTAMP ,'+00:00','-8:00'));

Something similar in sqlite:

SELECT time(CURRENT_TIMESTAMP, 'localtime')
or
SELECT time(CURRENT_TIMESTAMP, '-8 hours')

Will work too.

11

What type of column are you saved the timestamp in?