PostgreSQL

任意の日付・時間の範囲を生成

More than 1 year has passed since last update.


2016/7/1〜2016/7/3迄の10:00〜13:00を1時間毎に

select *

from (select to_char(d.d, 'YYYY-MM-DD ') || to_char(h.t, 'HH24:MI:SS') as dt

from (select * from generate_series('2016-07-01 10:00:00'::timestamp, '2016-07-01 13:00:00', '1 hour')) as h(t),

(select * from generate_series('2016-07-01 00:00:00'::timestamp, '2016-07-03 00:00:00', '1 day')) as d(d)) asf

order by dt

-- 時間生成の方を2016/7/1で作ってるけど、1/1/1とかのほうがわかりやすかったかな


出力結果

'2016-07-01 10:00'

'2016-07-01 11:00'

'2016-07-01 12:00'

'2016-07-01 13:00'

'2016-07-02 10:00'

'2016-07-02 11:00'

'2016-07-02 12:00'

'2016-07-02 13:00'

'2016-07-03 10:00'

'2016-07-03 11:00'

'2016-07-03 12:00'

'2016-07-03 13:00'