SQL - extract time and offset from it

Question

Hi,

If I have a DateTime field within a table. How can I extract just the time from the row and then return an offset value.

For example, I want to offset against midnight. So if the time is 00:45, I want the value 45. If the time is 23:30 I want the value -30.

Thanks.

Answer 1

Try:

(datecol - round(datecol))*24*60

For example:

with times as
( select trunc(sysdate) t from dual
  union
  select trunc(sysdate)+0.25 t from dual
  union
  select trunc(sysdate)+0.5 t from dual
  union
  select trunc(sysdate)+0.75 t from dual
)
select (t-round(t))*24*60 from times;

0
360
-720
-360

Note that midday is treated as 720 minutes before midnight.

Answer 2

A list of Oracle-supported datetime formats can be found here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm

You can use these formats with the to_char function - for example:

select to_char(sysdate, 'mi') from dual

You seem to have picked a way of representing times that isn't among those supported, but Tony's answer should meet your requirements.

Answer 3

Ok..assuming time from 12 to 23 hrs as before midnight and 0 to 11 hrs as after midnight, in other words as @Tony Andrews said midday is treated as 720 minutes before midnight

Here is another solution -

SELECT (
  CASE
    WHEN TO_CHAR(datetimefield,'HH24') BETWEEN 12 AND 23
    THEN -1 * (60 * (12 - TO_CHAR(datetimefield,'HH')) - TO_CHAR(datetimefield,'MI'))
    WHEN TO_CHAR(datetimefield,'HH24') BETWEEN 0 AND 11
    THEN 60 * (TO_CHAR(datetimefield,'HH')) + TO_CHAR(datetimefield,'MI')
  END) diff
FROM (select systimestamp datetimefield from dual);

I used my systemtimestamp for testing, You need to replace the the query select systimestamp datetimefield from dual to fetch your own datetime field from your table to make it work for you.