oracle sql date format to only seconds

Question

So I'm trying to convet a timestamp to seconds.

I read that you could do it this way

to_char(to_date(10000,'sssss'),'hh24:mi:ss')

But turns out this way you can't go over 86399 seconds.

This is my date format: +000000000 00:00:00.000000

What's the best approach to converting this to seconds? (this is the result of subtracting two dates to find the difference).

You subtracted timestamp1 from timestamp2 and want to know how many seconds that is? — Dan Bracuk, May 16 '14 at 15:52

eliatou · Answer 1 · 2014-05-16T16:23:10.987

3

You could convert timestamp to date by adding a number (zero in our case). Oracle downgrade then the type from timestamp to date

ex:

select systimestamp+0  as sysdate_ from dual

and the difference in secondes between 2 timestamp:

SQL> select 24*60*60*
       ((SYSTIMESTAMP+0)
        -(TO_TIMESTAMP('16-MAY-1414:10:10.123000','DD-MON-RRHH24:MI:SS.FF')+0)
        ) 
     diff_ss from dual;

   DIFF_SS
 ----------
   15140

edited May 16 '14 at 16:23

answered May 16 '14 at 15:56

eliatou

744
5
12

While I love this solution, I'm getting a rather peculiar issue: ORA-00932: inconsistent datatypes: expected - got INTERVAL DAY TO SECOND 00932. 00000 - "inconsistent datatypes: expected %s got %s" – Stupid.Fat.Cat May 16 '14 at 17:14
2

Wouldn't it be clearer - particularly for whoever has to maintain the code - to do `cast(systimestamp as date)` etc. rather than add the arbitrary zero and rely on the date time arithmetic? – Alex Poole May 16 '14 at 18:02
@Stupid.Fat.Cat: Could you give up your code? For me it working perfectly – eliatou May 19 '14 at 09:15
@Alex Poole. You're right, doing a cast(systimestamp as date) is maybe clearer. But for me the most clearer is a simple comment to explain why you are doing that! For me I often do that when I have to read some column with timestamp just because it's human readable! So +0 is easier to write than a cast. – eliatou May 19 '14 at 09:19

Allan · Accepted Answer · 2014-05-16T16:06:43.500

2

It looks like you're trying to find the total number of seconds in an interval (which is the datatype returned when you subtract two timestamps). In order to convert the interval to seconds, you need to extract each component and convert them to seconds. Here's an example:

SELECT interval_value,
         (EXTRACT (DAY FROM interval_value) * 24 * 60 * 60)
       + (EXTRACT (HOUR FROM interval_value) * 60 * 60)
       + (EXTRACT (MINUTE FROM interval_value) * 60)
       + EXTRACT (SECOND FROM interval_value)
          AS interval_in_sec
FROM   (SELECT SYSTIMESTAMP - TRUNC (SYSTIMESTAMP - 1) AS interval_value
        FROM   DUAL)

edited May 16 '14 at 16:06

answered May 16 '14 at 16:00

Allan

17,141
4
52
69

SYSTIMESTAMP - 1 convert your timestamp to a date! – eliatou May 16 '14 at 16:13
@eliatou: True, but subtracting a `date` from a `timestamp` results in an `interval`, which is the point of the exercise. – Allan May 16 '14 at 17:07

Dan Bracuk · Answer 3 · 2014-05-16T16:19:05.333

0

If you want the number of seconds between two dates (or timestamps),

select floor(
(to_date(to_char(timestamp1, 'yyyy-mm-dd HH24:MI:ss'),'yyyy-mm-dd HH24:MI:ss') 
- to_date(to_char(timestamp2, 'yyyy-mm-dd HH24:MI:ss'),'yyyy-mm-dd HH24:MI:ss')
 )    
* 24 -- hours per day
* 60 -- minutes per hour
* 60 -- seconds per minute
)
etc

edited May 16 '14 at 16:19

answered May 16 '14 at 15:55

Dan Bracuk

20,699
4
26
43

This only works for `date` types, not `timestamp`. Subtracting two `timestamp` values results in an `interval`, which can't be operated on as simply. – Allan May 16 '14 at 16:02
That is correct. Answer edited accordingly. – Dan Bracuk May 16 '14 at 16:19
2

Wouldn't it be clearer - particularly for whoever has to maintain the code - and simpler to do `cast(timestamp1 as date)` etc. rather than converting to and from strings? – Alex Poole May 16 '14 at 18:06

oracle sql date format to only seconds

3 Answers3