Request a Demo
Home   >   mailing lists

Re: Selecting consecutive dates as integers in yyyymmdd format - Mailing list pgsql-sql

From bricklen
Subject Re: Selecting consecutive dates as integers in yyyymmdd format
Date
Msg-id CAGrpgQ-yDt85-u6EL4qsr-0aa6XB=sU-2=1X1oGVP-_WYt9tTw@mail.gmail.com
Whole thread
In response to Selecting consecutive dates as integers in yyyymmdd format  (AlexK <alkuzo@gmail.com>)
List pgsql-sql
Tree view

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
...

pgsql-sql by date:

Previous
From: AlexK
Date:
Subject: Selecting consecutive dates as integers in yyyymmdd format
Next
From: Pavel Stehule
Date:
Subject: Re: How to unnest an array with element indexes