Time difference in SQL between record and "a specific time on the day"

I have a tabel in a relation database which contains a lot of dates. I my application logic I have divided one day into 4 parts of 6 hours each, starting at: 00:00, 06:00, 12:00 and 18:00. Now I would like to find the time difference of the earliest record in the database for each quater of a day, and the beginning og the peiod. How can I do that?

In psuedo-sql i guess it looks like

select min(created_at - ROUND_DOWN_TO_6_HOURS(created_at)) from mytabel group by day_quater;

The problem is how to calculate "ROUND_DOWN_TO_6_HOURS开发者_高级运维". So if "created_at" is 19:15 it will be rounded down to 18:00 and "created_at - ROUND_DOWN_TO_6_HOURS(created_at)" will return 1:15 hourd

I'm working with psql

If you're just trying to locate the records that match these ranges, you could just use that in the WHERE clause like

select * from myTable 
where datepart(hh, created_at) between 0 and 6

If your trying to create a computed field that will have the 00 or 06 ... then you could use the "DatePart()" function in sql to pull the hour... DATEPART ( hh, date )... This would return a numeric value of 0, 1, 2, 3, ... 23 and you can compute a field based on this value being between 2 of your hours listed...

Here's a sample...

select 
case  
   when datepart(hh, add_dt) between 0 and 6 then 1 
   when datepart(hh, add_dt) between 7 and 12 then 2 
   when datepart(hh, add_dt) between 13 and 18 then 3 
   when datepart(hh, add_dt) between 19 and 24 then 4 
end  
from myTable 
where add_dt is not null

You could use CASE in conjunction with your date column and datetime functions to establish the quarter-of-day (1,2,3,4) and extract the day part from the datetime value, group by day, quarter, and then use the MIN(yourdatecolumn) to grab the earliest time within each quarter grouping.

Not sure what you mean by "beginning of the period". but you can measure the difference between any arbitrary datetime and your set of earliest times per day-quarter which was instantiated in the manner above.

http://www.postgresql.org/docs/8.2/static/functions-datetime.html

select 
  record::time - (case
     when record::time >= '18:00' then '18:00'
     when record::time >= '12:00' then '12:00'
     when record::time >= '6:00' then '6:00'
     else '0:00' end
     )::time as difference
from my_table

My PostgreSQL is a little rusty, but something like this:

select
    date_trunc('day',CreatedOn) [Day],
    min(case when date_part('hour',TIMESTAMP CreatedOn) < 6 then '00:00'
          when date_part('hour',TIMESTAMP CreatedOn) < 12 then '06:00'
          when date_part('hour',TIMESTAMP CreatedOn) < 18 then '12:00'
          else '18:00'
         end) [Quarter]
from MyTable
group by date_trunc('day',CreatedOn)
order by date_trunc('day',CreatedOn)