CODEWITHSUNDEEP
sqloracle-database
Georg Leber
Georg Leber

Reputation: 3605

Compare date + time with timestamp

I have a table with two temporal columns. First (name is DATE) is storing the date (not including the time part) and therefor the datatype is DATE. Second column (name is TIME) is for storing the time in seconds and therefor the datatype is NUMBER.

I need to compare this two dates with a timestamp from another table. How can I calculate the date of the two columns (DATE and TIME) and compare to the timestamp of the other table?

I have tried to calculate the hours out of the time column and add it to the date column, but the output seems not correct:

SELECT to_date(date + (time/3600), 'dd-mm-yy hh24:mi:ss') FROM mytable;

The output is just the date, but not the time component.

Upvotes: 0

Views: 2528

Answers (4)

Alex Poole
Alex Poole

Reputation: 191245

date + (time/3600) is already a DATE, so you don't need to do to_date(). It does have the time part you added though, you just aren't displaying it. If you want to output that as a string in the format you've shown, use to_char() instead:

SELECT to_char(date + (time/3600), 'dd-mm-yy hh24:mi:ss') FROM mytable;

... except that if time is actually in seconds, you need to divide by 86400 (24x60x60), not 3600. At the moment you're relying on your client's default date format, probably NLS_DATE_FORMAT, which doesn't include the time portion from what you've said. That doesn't mean the time isn't there, it just isn't displayed.

But that is just for display. Leave it as a date, by just adding the two values, when comparing against you timestamp, e.g.

WHERE date + (time/86400) < systimestamp

Upvotes: 2

ablehunder
ablehunder

Reputation: 11

try using to_timestamp instead of to_date

Upvotes: 0

Dba
Dba

Reputation: 6639

Try like this,

 SELECT TO_DATE('11/11/2013','dd/mm/yyyy') + 3600/60/60/24 FROM DUAL;

Your query, 

SELECT date + time/60/60/24 FROM mytable;

Upvotes: 0

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

You can use the INTERVAL DAY TO SECOND type:

SELECT your_date + NUMTODSINTERVAL(your_time_in_seconds, 'SECOND') FROM dual;

Example:

SELECT TRUNC(SYSDATE) + NUMTODSINTERVAL(39687, 'SECOND') FROM dual;

The calculated date with time is: 10-11-2013 11:01:27

This is a better idea than dividing your value by 3600 in my opinion, as you have an interval in seconds, so it feels natural to use an interval to represent your time, which can then be easily added to a column of DATE datatype.

Oracle Interval in Documentation

NUMTODSINTERVAL Function in documentation

Upvotes: 3

Related Questions