More than 5 years have passed since last update.

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

PostgreSQL

Posted at 2016-06-16

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

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'

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up