Thread: 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