CODEWITHSUNDEEP
sqlpostgresql
dwool
dwool

Reputation: 29

How to extract the hour of day from an epoch and count each instance that occurs during that hour?

I have a question that I feel is pretty straight forward but is giving me some issues.

I have a column in table X called event_time which is an epoch. I am wanting to extract the hour of day out of that and count the number of rides that have occurred during that hour.

So the output will end up being a bar chart with x values 0-24 and the Y being the number of instances that occur (which is bike rides for example).

Here is what I have now, that isn't giving me the correct output:

select extract(hour from to_timestamp(start_time)::date) as hr,
count(*) as ct
from x 
group by hr 
order by hr asc

Any hints or help are appreciated.

Thanks

Upvotes: 0

Views: 1003

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You can use arithmetic:

select floor( (start_time % (24 * 60 * 60)) / (60 * 60) ) as hour,
       count(*)
from x
group by hour;

Or convert to a date/time and extract the hour:

select extract(hour from '1970-01-01'::date + start_time * interval '1 second') as hour, count(*)
from x
group by hour;

Upvotes: 1

Related Questions