Grouping Millis by Date and Hour

Moderator: NorbertKrupa

Post Reply
alhxnrt
Newbie
Newbie
Posts: 2
Joined: Wed Aug 23, 2017 3:49 pm

Grouping Millis by Date and Hour

Post by alhxnrt » Wed Aug 23, 2017 3:55 pm

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 :)

sKwa
Newbie
Newbie
Posts: 11
Joined: Wed Aug 02, 2017 3:12 pm

Re: Grouping Millis by Date and Hour

Post by sKwa » Wed Aug 23, 2017 7:09 pm

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)


alhxnrt
Newbie
Newbie
Posts: 2
Joined: Wed Aug 23, 2017 3:49 pm

Re: Grouping Millis by Date and Hour

Post by alhxnrt » Thu Aug 24, 2017 2:51 pm

Thanks!

Post Reply

Return to “New to Vertica”