Skip site navigation (1) Skip section navigation (2)

Generate a list of (days/hours) between two dates

From: ben(dot)hallert(at)gmail(dot)com
To: pgsql-general(at)postgresql(dot)org
Subject: Generate a list of (days/hours) between two dates
Date: 2005-06-27 17:30:38
Message-ID: 1119893438.380710.219440@f14g2000cwb.googlegroups.com (view raw or flat)
Thread:
Lists: pgsql-general
Hi guys,

I've scoured the date/time functions in the docs as well as
google-grouped as many different combinations as I could think of to
figure this out without asking, but I'm having no luck.

I'd like to make a query that would return a list of every trunc'd
TIMESTAMPs between two dates.  For example, I'd want to get a list of
every date_trunc('hour',whatever) between 6-1-2005 and 6-10-2005 and
get a list that looks like:

6-1-2005 00:00:00
6-1-2005 01:00:00
6-1-2005 02:00:00
etc

Conversely, I want to generate a list of every day between two dates,
like:

6-1-2005 00:00:00
6-2-2005 00:00:00
6-3-2005 00:00:00

I know there's gotta be some way to do this in a SELECT function, but
I'm running into a brickwall.  I'm trying to take some of my date
handling logic out of code and use the db engine so I can spend less
time developing/maintaining code when mature date handling already
exists in a resource I've already got loaded.

Any thoughts?


Responses

pgsql-general by date

Next:From: Alexander KorobovDate: 2005-06-27 17:35:12
Subject: problems with slow insert/delete queries/lot of disk write i/o in postgresql 7.2.4
Previous:From: Magnus HaganderDate: 2005-06-27 16:47:11
Subject: Re: DANGER Windows version might hurt you

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group