# Insert local time in db

**URL:** <https://discourse.nodered.org/t/insert-local-time-in-db/19948>\
**Category:** General\
**Created:** [6 January 2020 04:53 UTC](https://discourse.nodered.org/t/insert-local-time-in-db/19948 "2020-01-06T04:53:58Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![NODE777](https://avatars.discourse-cdn.com/v4/letter/n/7993a0/32.png) [@NODE777](https://discourse.nodered.org/u/NODE777)\
**Post date:** [6 January 2020 04:53 UTC](https://discourse.nodered.org/t/insert-local-time-in-db/19948/1 "2020-01-06T04:53:58Z")

</div>

I have created the following db with ;

```auto
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).

```auto

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:

```auto
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 !

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [6 January 2020 06:39 UTC](https://discourse.nodered.org/t/insert-local-time-in-db/19948/2 "2020-01-06T06:39:57Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [6 January 2020 07:25 UTC](https://discourse.nodered.org/t/insert-local-time-in-db/19948/3 "2020-01-06T07:25:46Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Christian-Me](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/christian-me/32/10774_2.png) [@Christian-Me](https://discourse.nodered.org/u/Christian-Me)\
**Post date:** [6 January 2020 08:42 UTC](https://discourse.nodered.org/t/insert-local-time-in-db/19948/4 "2020-01-06T08:42:53Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [6 January 2020 10:20 UTC](https://discourse.nodered.org/t/insert-local-time-in-db/19948/5 "2020-01-06T10:20:19Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![NODE777](https://avatars.discourse-cdn.com/v4/letter/n/7993a0/32.png) [@NODE777](https://discourse.nodered.org/u/NODE777)\
**Post date:** [6 January 2020 20:02 UTC](https://discourse.nodered.org/t/insert-local-time-in-db/19948/6 "2020-01-06T20:02:16Z")

</div>

@TotallyInformation @Colin@bakman2 @Christian-Me

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

---

<div class="post-metadata">

**Author:** ![NODE777](https://avatars.discourse-cdn.com/v4/letter/n/7993a0/32.png) [@NODE777](https://discourse.nodered.org/u/NODE777)\
**Post date:** [15 January 2020 03:56 UTC](https://discourse.nodered.org/t/insert-local-time-in-db/19948/7 "2020-01-15T03:56:33Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/6/c6ad46a0eb5bbe9e46a7d3e1b3efc2c8e5ec21f0.png)  
The following is the debug tab showing the Sqlite node output and Moment node output.

```auto

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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [15 January 2020 09:05 UTC](https://discourse.nodered.org/t/insert-local-time-in-db/19948/8 "2020-01-15T09:05:55Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![NODE777](https://avatars.discourse-cdn.com/v4/letter/n/7993a0/32.png) [@NODE777](https://discourse.nodered.org/u/NODE777)\
**Post date:** [16 January 2020 04:53 UTC](https://discourse.nodered.org/t/insert-local-time-in-db/19948/9 "2020-01-16T04:53:09Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [16 January 2020 07:08 UTC](https://discourse.nodered.org/t/insert-local-time-in-db/19948/10 "2020-01-16T07:08:18Z")

</div>

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

If you do a query like:

```auto
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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [16 January 2020 07:10 UTC](https://discourse.nodered.org/t/insert-local-time-in-db/19948/11 "2020-01-16T07:10:44Z")

</div>

> [@NODE777](#):
>
> Yes, I'm saving the complete UTC timestamp in the database.!

What type of column are you saved the timestamp in?