Hi,
I have one more I would like a query that returns the range of dates that fall in between two dates?
Thanks, again!
Return a range of dates between 2 given dates
Moderator: NorbertKrupa
Re: Return a range of dates between 2 given dates
Hi!
if you need as series use in subquery: "select [...] from [...] where [...] in <query above>"
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)
Re: Return a range of dates between 2 given dates
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?
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?
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Return a range of dates between 2 given dates
piglet,
You can easily modify sKwa's awesome example to meet your requirement:
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
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Return a range of dates between 2 given dates
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):
@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)
Re: Return a range of dates between 2 given dates
Cool! Thank guys! sKwa, which option would you recommend for best performance?