Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2010
    Posts
    40

    Unanswered: date_part hour to find records before and after hour

    Hi all,
    I'm trying to generate a query from a dataset which includes a timestamp column. I want to extract rows where the hour portion is between a start and end hour.

    It all works fine provided the start hour is less than the end hour but I can't see how to get it say for

    starthour=21
    endhour=3

    where I want records between 9pm and 3am, regardless as to the day.

    Now I could see adding some logic where I first check if start < end, then check from 21 to 0 plus 0 to 3 but thought maybe there was some better way.

    I'm using date_part('hour',mytimecol) >= starthour and date_part('hour',mytimecol) < endhour

    If I did need the logic to check which is greater, I'm not entirely sure how I would do it in sql anyway.

    Thanks

  2. #2
    Join Date
    Aug 2011
    Posts
    42
    In case your basis of time is a day, then this solution scans as per a given timestamp and compares with current datetime

    select col1, mytimecol from table1 where EXTRACT(EPOCH FROM etime) > EXTRACT(EPOCH FROM current_timestamp)-(24*3600)-1
    and EXTRACT(EPOCH FROM etime) < EXTRACT(EPOCH FROM current_timestamp)+(24*3600)+1
    order by col1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •