# Human readable time to mysql

**URL:** <https://discourse.nodered.org/t/human-readable-time-to-mysql/17689>\
**Category:** General\
**Created:** [8 November 2019 21:24 UTC](https://discourse.nodered.org/t/human-readable-time-to-mysql/17689 "2019-11-08T21:24:29Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![moment](https://avatars.discourse-cdn.com/v4/letter/m/e495f1/32.png) [@moment](https://discourse.nodered.org/u/moment)\
**Post date:** [8 November 2019 21:24 UTC](https://discourse.nodered.org/t/human-readable-time-to-mysql/17689/1 "2019-11-08T21:24:29Z")

</div>

i'm using interval node and inserting data to mysql ,everything works if i insert the time in milliseconds...  
i want to insert it directly into human readable form, the interval node outputs the human readable as 1m 30s and for some reason i can't insert that into mysql  
i tried different columns time, int, varchar nothing seems to work is there another node that can transform time to mysql format? i'l take any help i can get

---

<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:** [8 November 2019 22:07 UTC](https://discourse.nodered.org/t/human-readable-time-to-mysql/17689/2 "2019-11-08T22:07:39Z")

</div>

> [@moment](#):
>
> i want to insert it directly into human readable form,

Why? Usually that is a bad idea, put it in the db in a `timestamp` column (in UTC) then convert it for display in your particular timezone when you read it out. That way you can query on timestamp ranges and so on. Also applications such as Grafana expect the timestamp in that form.

---

<div class="post-metadata">

**Author:** ![moment](https://avatars.discourse-cdn.com/v4/letter/m/e495f1/32.png) [@moment](https://discourse.nodered.org/u/moment)\
**Post date:** [8 November 2019 23:03 UTC](https://discourse.nodered.org/t/human-readable-time-to-mysql/17689/3 "2019-11-08T23:03:37Z")

</div>

Colin it's a counter it counts the time between payloads why would i put it in a timestamp it doesn't make sense , i'm trying to have a sort of reporting StartDate EndDate and counter...i already have a timestamp for StartDate ...  
it wont make sense to put 1m and 30 s in milliseconds in timestamp unless i'm wrong...and an int column would do the same thing u can add and subtract with the timestamp...i just want to insert it in human readable form so i don't have to do extra query's

---

<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:** [8 November 2019 23:27 UTC](https://discourse.nodered.org/t/human-readable-time-to-mysql/17689/4 "2019-11-08T23:27:31Z")

</div>

> 1m 30s

is not an int(eger) and not time, but it is a varchar and you should be able to insert it.  
Then again, to keep records an actual timestamp is a better idea.

---

<div class="post-metadata">

**Author:** ![moment](https://avatars.discourse-cdn.com/v4/letter/m/e495f1/32.png) [@moment](https://discourse.nodered.org/u/moment)\
**Post date:** [8 November 2019 23:35 UTC](https://discourse.nodered.org/t/human-readable-time-to-mysql/17689/5 "2019-11-08T23:35:30Z")

</div>

i tried to insert it in a varchar 60 column ,the column stays null or more exactly zero (0)  
and i am pretty sure u can't insert milliseconds in a timestamp it will stay as 0  
i'm using the same query in the function node it works with milliseconds but it wont work with "1m30s" i can see the output in the debug node, it returns an error ( check your maria db ta ta ta usual error)

---

<div class="post-metadata">

**Author:** ![moment](https://avatars.discourse-cdn.com/v4/letter/m/e495f1/32.png) [@moment](https://discourse.nodered.org/u/moment)\
**Post date:** [8 November 2019 23:39 UTC](https://discourse.nodered.org/t/human-readable-time-to-mysql/17689/6 "2019-11-08T23:39:58Z")

</div>

it's nothing to complicated gpioin node----\> interval-length node----\> function node-----\>mysql

i was hoping maybe there was a node that would transform the time to mysql friendly time...without the m and s it should be 00 01 30

---

<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:** [9 November 2019 06:39 UTC](https://discourse.nodered.org/t/human-readable-time-to-mysql/17689/7 "2019-11-09T06:39:54Z")

</div>

Pretty sure there is something incorrect in your query. Did you enclose the field in single quotes ?

---

<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:** [9 November 2019 09:06 UTC](https://discourse.nodered.org/t/human-readable-time-to-mysql/17689/8 "2019-11-09T09:06:54Z")

</div>

Post the output of a debug node showing what you are feeding to your sql node, and the error that the sql node is generating.

---

<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:** [9 November 2019 11:05 UTC](https://discourse.nodered.org/t/human-readable-time-to-mysql/17689/9 "2019-11-09T11:05:35Z")

</div>

Personally, I would store it as a BIGINT in milliseconds. Then you can use it both for display and for calculations.

---

<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:** [9 November 2019 11:13 UTC](https://discourse.nodered.org/t/human-readable-time-to-mysql/17689/10 "2019-11-09T11:13:07Z")

</div>

> [@TotallyInformation](#):
>
> I would store it as a BIGINT in milliseconds

+1 to that.

---

<div class="post-metadata">

**Author:** ![moment](https://avatars.discourse-cdn.com/v4/letter/m/e495f1/32.png) [@moment](https://discourse.nodered.org/u/moment)\
**Post date:** [11 November 2019 22:18 UTC](https://discourse.nodered.org/t/human-readable-time-to-mysql/17689/11 "2019-11-11T22:18:14Z")

</div>

that's what i was doing totally ,but since i don't really need it for any calculations i wanted to insert it as is,i'm just gonna generate a column

Thank you guys