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

timestamp interval issue

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 (view raw or flat)
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

Responses

pgsql-novice by date

Next:From: Valentin GjorgjioskiDate: 2007-10-05 23:08:39
Subject: Re: timestamp interval issue
Previous:From: Tore LukashaugenDate: 2007-10-03 18:56:57
Subject: Re: Simple sql question

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