Return a range of dates between 2 given dates

Moderator: NorbertKrupa

User avatar
piglet
Beginner
Beginner
Posts: 45
Joined: Tue Feb 07, 2012 4:04 pm

Return a range of dates between 2 given dates

Post by piglet » Wed Sep 11, 2013 7:23 pm

Hi,

I have one more :) I would like a query that returns the range of dates that fall in between two dates?

Thanks, again!

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Return a range of dates between 2 given dates

Post by id10t » Wed Sep 11, 2013 8:03 pm

Hi!

Code: Select all

\set min       '''2013-02-26 00:00:00'''
\set max       '''2013-09-11 00:00:00'''
\set interval  '''14 DAYS 12 HOURS 47 MINUTES'''

SELECT ts
  FROM (SELECT :min::TIMESTAMP as tm
        UNION
        SELECT :max::TIMESTAMP as tm) as t
TIMESERIES ts as :interval OVER (ORDER BY tm);

Code: Select all

daniel=> \g
         ts          
---------------------
 2013-02-16 18:30:00
 2013-03-03 07:17:00
 2013-03-17 20:04:00
 2013-04-01 08:51:00
 2013-04-15 21:38:00
 2013-04-30 10:25:00
 2013-05-14 23:12:00
 2013-05-29 11:59:00
 2013-06-13 00:46:00
 2013-06-27 13:33:00
 2013-07-12 02:20:00
 2013-07-26 15:07:00
 2013-08-10 03:54:00
 2013-08-24 16:41:00
 2013-09-08 05:28:00
(15 rows)
if you need as series use in subquery: "select [...] from [...] where [...] in <query above>"

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Return a range of dates between 2 given dates

Post by id10t » Wed Sep 11, 2013 9:13 pm

UPDATE:

Follow to this (if you mind): https://community.vertica.com/vertica/t ... t_properly

User avatar
piglet
Beginner
Beginner
Posts: 45
Joined: Tue Feb 07, 2012 4:04 pm

Re: Return a range of dates between 2 given dates

Post by piglet » Thu Sep 12, 2013 12:35 pm

Thanks, sKwa, but I am looking to get just the dates that fall in between two other dates.

For instance, if I use 09/02/2013 and 09/13/2013, I'd like to only get the results:

09/02/2013
09/03/2013
09/04/2013
09/05/2013
09/06/2013
09/07/2013
09/08/2013
09/09/2013
09/10/2013
09/11/2013
09/12/2013
09/13/2013

Is that possible?

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Return a range of dates between 2 given dates

Post by JimKnicely » Thu Sep 12, 2013 3:18 pm

piglet,

You can easily modify sKwa's awesome example to meet your requirement:

Code: Select all

dbadmin=> SELECT ts::DATE
dbadmin->   FROM (SELECT '09/02/2013'::TIMESTAMP as tm
dbadmin(>         UNION
dbadmin(>         SELECT '09/13/2013'::TIMESTAMP as tm) as t
dbadmin-> TIMESERIES ts as '1 Day' OVER (ORDER BY tm);
     ts
------------
 2013-09-02
 2013-09-03
 2013-09-04
 2013-09-05
 2013-09-06
 2013-09-07
 2013-09-08
 2013-09-09
 2013-09-10
 2013-09-11
 2013-09-12
 2013-09-13
(12 rows)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Return a range of dates between 2 given dates

Post by id10t » Thu Sep 12, 2013 5:29 pm

Hi!

@piglet think!

Another option, at least you need table with X rows, for example in year 365 days. System tables are good choice too.

Example for week (week = 7 days => interval in days and limit 7):

Code: Select all

daniel=> select ('2013-02-25'::date + (row_number() over ())::INTERVAL DAY)::date from cpu_usage limit 7;
  ?column?  
------------
 2013-02-26
 2013-02-27
 2013-02-28
 2013-03-01
 2013-03-02
 2013-03-03
 2013-03-04
(7 rows)

User avatar
piglet
Beginner
Beginner
Posts: 45
Joined: Tue Feb 07, 2012 4:04 pm

Re: Return a range of dates between 2 given dates

Post by piglet » Thu Sep 12, 2013 9:29 pm

Cool! Thank guys! sKwa, which option would you recommend for best performance?

Post Reply

Return to “New to Vertica SQL”