Обсуждение: Selecting consecutive dates as integers in yyyymmdd format
The following code gets the job done, but I am wondering if there is a simpler/cleaner way? WITH start_date AS(SELECT DATE '2014-02-18' AS start_date) select CAST( to_char(start_date + INTERVAL '1d'*i,'YYYYMMDD') AS INT) AS end_range from generate_series(1,100) AS i CROSS JOIN start_date; end_range integer 20140219 20140220 20140221 20140222 20140223 20140224 20140225 20140226 20140227 20140228 20140301 20140302 (snip) -- View this message in context: http://postgresql.1045698.n5.nabble.com/Selecting-consecutive-dates-as-integers-in-yyyymmdd-format-tp5792815.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
On Wed, Feb 19, 2014 at 2:18 PM, AlexK <alkuzo@gmail.com> wrote:
The following code gets the job done, but I am wondering if there is a
simpler/cleaner way?
WITH start_date AS(SELECT DATE '2014-02-18' AS start_date)
select CAST( to_char(start_date + INTERVAL '1d'*i,'YYYYMMDD') AS INT) AS
end_range
from generate_series(1,100) AS i CROSS JOIN start_date;
You can use the other variation of generate_series to use dates directly:
select to_char(ymd,'YYYYMMDD') ymd from generate_series('2014-02-18'::date,'2014-03-18'::date,'1 day'::interval) ymd;
ymd
----------
20140218
20140219
20140220
20140221
20140222
20140223
...
select to_char(ymd,'YYYYMMDD') ymd from generate_series('2014-02-18'::date,'2014-03-18'::date,'1 day'::interval) ymd;
ymd
----------
20140218
20140219
20140220
20140221
20140222
20140223
...