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

Search Discussions

  • Valentin Gjorgjioski at Oct 5, 2007 at 11:19 pm

    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
    --
  • Lonni J Friedman at Oct 5, 2007 at 11:33 pm

    On 10/5/07, Valentin Gjorgjioski 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 && ...
    ^
  • Michael Glaesemann at Oct 5, 2007 at 11:37 pm

    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
  • Lonni J Friedman at Oct 5, 2007 at 11:44 pm

    On 10/5/07, 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?
    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.
  • Valentin Gjorgjioski at Oct 6, 2007 at 11:13 am

    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')
    )
  • Valentin Gjorgjioski at Oct 6, 2007 at 11:18 am

    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')
    )
  • Michael Glaesemann at Oct 5, 2007 at 11:35 pm

    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
  • Lonni J Friedman at Oct 5, 2007 at 11:46 pm

    On 10/5/07, Michael Glaesemann 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?
  • Michael Glaesemann at Oct 5, 2007 at 11:48 pm

    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
  • Lonni J Friedman at Oct 5, 2007 at 11:53 pm

    On 10/5/07, Michael Glaesemann 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!
  • Michael Glaesemann at Oct 5, 2007 at 11:57 pm

    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
  • Nis Jørgensen at Oct 8, 2007 at 10:46 am

    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 at Oct 8, 2007 at 11:15 am

    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
  • Michael Glaesemann at Oct 8, 2007 at 1:37 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedOct 5, '07 at 10:47p
activeOct 8, '07 at 1:37p
posts15
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase