Hey all!
Was hoping you guys could help me out.
I'm currently trying to group a millis column into Date and Hour.
For Example, the column is
Times
1484261834099
1484337025118
1484352178132
1484367983155
1484523555099
1484550424125
1484793955197
1484825845127
1485074682136
1485753969092
I'd like it to be:
2017-08-01 12:00
2017-08-01 13:00
2017-08-01 14:00
etc with multiple dates. I've tried DATE_PART, but can't work it out!
Thanks
Grouping Millis by Date and Hour
Moderator: NorbertKrupa
Re: Grouping Millis by Date and Hour
Hi!
dbadmin=> select
dbadmin-> mill,
dbadmin-> to_timestamp(mill / 1000) as ts,
dbadmin-> to_char(to_timestamp(mill / 1000), 'YYYY-MM-DD HH24:MI') as yh
dbadmin-> from ts;
mill | ts | yh
---------------+-------------------------+------------------
1484261834099 | 2017-01-13 00:57:14.099 | 2017-01-13 00:57
1484337025118 | 2017-01-13 21:50:25.118 | 2017-01-13 21:50
1484352178132 | 2017-01-14 02:02:58.132 | 2017-01-14 02:02
1484367983155 | 2017-01-14 06:26:23.155 | 2017-01-14 06:26
1484523555099 | 2017-01-16 01:39:15.099 | 2017-01-16 01:39
1484550424125 | 2017-01-16 09:07:04.125 | 2017-01-16 09:07
1484793955197 | 2017-01-19 04:45:55.197 | 2017-01-19 04:45
1484825845127 | 2017-01-19 13:37:25.127 | 2017-01-19 13:37
1485074682136 | 2017-01-22 10:44:42.136 | 2017-01-22 10:44
1485753969092 | 2017-01-30 07:26:09.092 | 2017-01-30 07:26
(10 rows)