Обсуждение: timestamp interval issue
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@gmail.com
LlamaLand https://netllama.linux-sxs.org
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
--
On 10/5/07, Valentin Gjorgjioski <tinodj@mt.net.mk> wrote:
> 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))
Thanks. Unfortunately, that doesn't seem to work. I get a syntax
error at the first colon:
ERROR: syntax error at or near ":" at character 55
LINE 1: ...ct id,last_update from footable0 where ((now>today:16:00 && ...
^
On Oct 5, 2007, at 17:47 , Lonni J Friedman wrote:
> 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.
Would something like this work for you?
select *
from footable0
where case when current_time < '16:00'::time
then date_created between (current_date - 1) + interval
'16 hours'
and current_date + interval '16 hours'
else date_created between current_date + interval '16 hours'
and (current_date + 1) + interval '16
hours';
I'm not sure if I'm interpreting what you need properly, but I think
this does what you want.
Michael Glaesemann
grzm seespotcode net
On Oct 5, 2007, at 18:08 , Valentin Gjorgjioski wrote: > where ((now>today:16:00 && date_created>today:16:00) || (now<today: > 16:00 && date_created>yesterday:16:00)) I assume he wanted SQL: what language is this? Michael Glaesemann grzm seespotcode net
On 10/5/07, Michael Glaesemann <grzm@seespotcode.net> wrote: > > On Oct 5, 2007, at 18:08 , Valentin Gjorgjioski wrote: > > > where ((now>today:16:00 && date_created>today:16:00) || (now<today: > > 16:00 && date_created>yesterday:16:00)) > > I assume he wanted SQL: what language is this? Yes, I was hoping for SQL. Barring that, I'll like have to hack up something in PHP to create a different SQL statement based on the time of day.
On 10/5/07, Michael Glaesemann <grzm@seespotcode.net> wrote: > > On Oct 5, 2007, at 17:47 , Lonni J Friedman wrote: > > > 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. > > Would something like this work for you? > > select * > from footable0 > where case when current_time < '16:00'::time > then date_created between (current_date - 1) + interval > '16 hours' > and current_date + interval '16 hours' > else date_created between current_date + interval '16 hours' > and (current_date + 1) + interval '16 > hours'; > > I'm not sure if I'm interpreting what you need properly, but I think > this does what you want. Thanks. I gave this a try (cut & paste exactly what you provided), but I'm getting a syntax error at the final semicolon?
On Oct 5, 2007, at 18:46 , Lonni J Friedman wrote:
> Thanks. I gave this a try (cut & paste exactly what you provided), but
> I'm getting a syntax error at the final semicolon?
Ah. I forgot the END of the CASE statement. Give this a try:
select count(date_created)
from footable0
where case when current_time < '16:00'::time
then date_created between (current_date - 1) + interval
'16 hours'
and current_date + interval '16 hours'
else date_created between current_date + interval '16 hours'
and (current_date + 1) + interval '16
hours'
end;
Michael Glaesemann
grzm seespotcode net
On 10/5/07, Michael Glaesemann <grzm@seespotcode.net> wrote: > > On Oct 5, 2007, at 18:46 , Lonni J Friedman wrote: > > > Thanks. I gave this a try (cut & paste exactly what you provided), but > > I'm getting a syntax error at the final semicolon? > > Ah. I forgot the END of the CASE statement. Give this a try: > > select count(date_created) > from footable0 > where case when current_time < '16:00'::time > then date_created between (current_date - 1) + interval > '16 hours' > and current_date + interval '16 hours' > else date_created between current_date + interval '16 hours' > and (current_date + 1) + interval '16 > hours' > end; That looks perfect. At least, after a few simple tests its returning expected results. I'll beat on this some more over the weekend. I didn't even realize that PostgreSQL supported conditional statements. Had I known, this would have been alot easier. Guess I need to RTFM alot more. Many thanks!
On Oct 5, 2007, at 18:53 , Lonni J Friedman wrote: > I didn't even realize that PostgreSQL supported conditional > statements. CASE is in the SQL standard, so any spec-compliant SQL dbms should support it. > Guess I need to RTFM alot more. There's a lot of good stuff in there. Michael Glaesemann grzm seespotcode net
On 06.10.2007 01:37 Michael Glaesemann wrote:
>
> On Oct 5, 2007, at 18:08 , Valentin Gjorgjioski wrote:
>
>> where ((now>today:16:00 && date_created>today:16:00) ||
>> (now<today:16:00 && date_created>yesterday:16:00))
>
> I assume he wanted SQL: what language is this?
>
ok, here it is ... I was not expecting that writing sql will be a problem...
where
(
now()>('today'::timestamp+interval '16 hours')
AND
date_created>('today'::timestamp+interval '16 hours')
)
OR
(
now()<('today'::timestamp+interval '16 hours')
AND
date_created>('today'::timestamp+interval '16 hours')
)
On 06.10.2007 13:10 Valentin Gjorgjioski wrote:
> On 06.10.2007 01:37 Michael Glaesemann wrote:
>>
>> On Oct 5, 2007, at 18:08 , Valentin Gjorgjioski wrote:
>>
>>> where ((now>today:16:00 && date_created>today:16:00) ||
>>> (now<today:16:00 && date_created>yesterday:16:00))
>>
>> I assume he wanted SQL: what language is this?
>>
>
> ok, here it is ... I was not expecting that writing sql will be a
> problem...
>
> where
> (
> now()>('today'::timestamp+interval '16 hours')
> AND
> date_created>('today'::timestamp+interval '16 hours')
> )
> OR
> (
> now()<('today'::timestamp+interval '16 hours')
> AND
> date_created>('today'::timestamp+interval '16 hours')
> )
There is a little error (last today should be yesterday)
where
(
now()>('today'::timestamp+interval '16 hours')
AND
date_created>('today'::timestamp+interval '16 hours')
)
OR
(
now()<('today'::timestamp+interval '16 hours')
AND
date_created>('yesterday'::timestamp+interval '16 hours')
)
Lonni J Friedman skrev: > 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 Something like this? SELECT * FROM footable0 WHERE (date_created - interval '16 hours')::date = (now() - interval '16 hours')::date; Tested. Nis
Nis Jørgensen skrev: > Lonni J Friedman skrev: >> 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. > SELECT * > FROM footable0 > WHERE (date_created - interval '16 hours')::date = (now() - interval '16 > hours')::date; > > Tested. Unfortunately, the performance is horrible - it is using a seqscan. Use Michaels suggestion instead (at least if you have a significant amount of data). Nis
On Oct 8, 2007, at 6:14 , Nis Jørgensen wrote: > Nis Jørgensen skrev: >> SELECT * >> FROM footable0 >> WHERE (date_created - interval '16 hours')::date = (now() - >> interval '16 >> hours')::date; >> >> Tested. > > Unfortunately, the performance is horrible - it is using a seqscan. You can create a functional index to improve performance here, something like create index rectified_created_at_idx on mytable (cast (created_at - interval '16 hours') to date); I haven't thought it through, but you may run into issues with time zones -- this is not particular to this solution however. Michael Glaesemann grzm seespotcode net