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

Re: timestamp interval issue

From: Valentin Gjorgjioski <tinodj(at)mt(dot)net(dot)mk>
To: Lonni J Friedman <netllama(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: timestamp interval issue
Date: 2007-10-05 23:08:39
Message-ID: 4706C3F7.5000106@mt.net.mk (view raw or flat)
Thread:
Lists: pgsql-novice
On 06.10.2007 00:47 Lonni J Friedman wrote:
> 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  ;

where ((now>today:16:00 && date_created>today:16:00) || (now<today:16:00 
&& date_created>yesterday:16:00))

-- 
Valentin Gjorgjioski
--
Human knowledge belongs to the world
--

In response to

Responses

pgsql-novice by date

Next:From: Valentin GjorgjioskiDate: 2007-10-05 23:16:47
Subject: Re: Simple sql question
Previous:From: Lonni J FriedmanDate: 2007-10-05 22:47:45
Subject: timestamp interval issue

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