Обсуждение: Hourly dates

Поиск
Список
Период
Сортировка

Hourly dates

От
Andrew Maclean
Дата:
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


--
___________________________________________
Andrew J. P. Maclean
Centre for Autonomous Systems
The Rose Street Building J04
The University of Sydney  2006  NSW
AUSTRALIA
Ph: +61 2 9351 3283
Fax: +61 2 9351 7474
URL: http://www.acfr.usyd.edu.au/
___________________________________________

Re: Hourly dates

От
Steve Crawford
Дата:
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

Re: Hourly dates

От
Andrew Maclean
Дата:
Thanks for this, I can easily determine the number of records I want.

Andrew


On Tue, Jun 23, 2009 at 10:52 AM, Steve
Crawford<scrawford@pinpointresearch.com> wrote:
> 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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



--
___________________________________________
Andrew J. P. Maclean
Centre for Autonomous Systems
The Rose Street Building J04
The University of Sydney  2006  NSW
AUSTRALIA
Ph: +61 2 9351 3283
Fax: +61 2 9351 7474
URL: http://www.acfr.usyd.edu.au/
___________________________________________