From: | "Lonni J Friedman" <netllama(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | timestamp interval issue |
Date: | 2007-10-05 22:47:45 |
Message-ID: | 7c1574a90710051547w49b1d3dcr59e6e705d701beb4@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Greetings,
I've got an interesting problem. I have a table with a column full of
timestamps. I need a means of returning only the rows which have a
timestamp that falls after the last 16:00 and before the next 16:00
(on the clock), regardless of date.
For example, let's say I've got this in my table:
date_created
----------------------
10-05-2007 00:44:45
10-04-2007 17:59:43
10-04-2007 19:12:00
10-04-2007 17:59:54
10-03-2007 21:00:56
10-04-2007 19:12:00
10-03-2007 21:00:58
and let's say that the current timestamp (select now()) returns:
2007-10-05 15:18:54.133368-07
I need to get back just the following rows:
10-05-2007 00:44:45
10-04-2007 17:59:43
10-04-2007 19:12:00
10-04-2007 17:59:54
10-04-2007 19:12:00
The closest I've come is the following, which unfortunately only works
if its not yet after 16:00 on the current date:
select count(id) from footable0 where ('today'::timestamp - interval
'8 hours') < date_created::timestamp AND ('tomorrow'::timestamp +
interval '8 hours') > date_created::timestamp ;
thanks for all help, suggestions & input.
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
L. Friedman netllama(at)gmail(dot)com
LlamaLand https://netllama.linux-sxs.org
From | Date | Subject | |
---|---|---|---|
Next Message | Valentin Gjorgjioski | 2007-10-05 23:08:39 | Re: timestamp interval issue |
Previous Message | Tore Lukashaugen | 2007-10-03 18:56:57 | Re: Simple sql question |