Andrew Maclean wrote:
> Is this the best way of getting a table of hourly dates?
>
> -- How to generate a table of dates at hourly intervals between two dates.
>
> -- select timestamp 'epoch' + generate_series * interval '1 second' as
> dates from generate_series(extract(epoch from date_trunc('hour',
> timestamp '2001-02-16 20:38:40'))::bigint,extract(epoch from
> date_trunc('hour', timestamp '2001-02-17 20:38:40'))::bigint, 3600)
> select generate_series * interval '1 second' + date_trunc('hour',
> timestamp '2001-02-16 20:38:40') as dates
> from generate_series(0,extract(epoch from(date_trunc('hour',
> timestamp '2001-02-17 20:38:40') - date_trunc('hour', timestamp
> '2001-02-16 20:38:40')))::bigint, 3600)
>
> The commented out query seems to take into account the timezone which
> is not what I want.
>
> Andrew
>
>
>
Depends on what you have available as input. If you know the starting
time and number of records it's pretty easy:
Without time-zone:
select '2009-03-05 0100'::timestamp + generate_series(0,100) * '1
hour'::interval;
...
2009-03-07 23:00:00
2009-03-08 00:00:00
2009-03-08 01:00:00
2009-03-08 02:00:00
2009-03-08 03:00:00
2009-03-08 04:00:00
...
With time-zone info:
select '2009-03-05 0100'::timestamptz + generate_series(0,100) * '1
hour'::interval;
...
2009-03-07 23:00:00-08
2009-03-08 00:00:00-08
2009-03-08 01:00:00-08
2009-03-08 03:00:00-07
2009-03-08 04:00:00-07
2009-03-08 05:00:00-07
2009-03-08 06:00:00-07
...
Cheers,
Steve