Timestamp to number

Question

How to convert the CURRENT_TIMESTAMP function in a NUMBER using ORACLE?

Using the following code, I'm able to extract the CURRENT_TIMESTAMP of the system

SELECT SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) FROM DUAL;

What I want is to use the result of the above code to create the corresponding timestamp in unix format. Pratically: 1) with SELECT SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) FROM DUAL; i receive this result:

--------------------------------------------------
|    SYS_EXTRACT_UTC(CURRENT_TIMESTAMP)          |
--------------------------------------------------
|       30-OTT-14 09:51:43,164232000             |
--------------------------------------------------

2) And I want to convert it to:

--------------------------------------------------
|               UNIXTIMESTAMP                    |
--------------------------------------------------
|                 1414662703                     |
--------------------------------------------------

Is that possible? Thank you all in advance

Did you saw this? http://stackoverflow.com/questions/12105691/convert-timestamp-datatype-into-unix-timestamp-oracle — Dmitriy, Oct 30 '14 at 10:19

score 5 · Answer 1 · answered Oct 30 '14 at 10:19

5

if you need to work with timestamps than you may try this

select trunc(
         extract(day from intvl) * 24 * 60 * 60 
       + extract(hour from intvl) * 60 * 60 
       + extract(minute from intvl) * 60
       + extract(second from intvl)
       )
from
(select SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) - to_timestamp('19700101', 'YYYYMMDD') intvl from dual);

The difference between two timestamps is an interval

EXTRACT(fmt FROM INTERVAL) extracts date time component from an INTERVAL

TRUNC truncates milliseconds

answered Oct 30 '14 at 10:19

Multisync

8,657
1
16
20

From his example, where the `unixtimestamp` value has no decimal places, he apparently does not need to work with the precision of timestamps. That aside, your solution is, of course, correct and nice. – peter.hrasko.sk Oct 30 '14 at 10:24
@nop77svk Yes it's true. It's enough to used dates, but not the current_date because this date is in session's timezone. As I understand the OP needs UTC time. – Multisync Oct 30 '14 at 10:33
@nop77svk CAST(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) as DATE) instead of current_date would be more precise – Multisync Oct 30 '14 at 10:35
Absolutely correct. Thank you for noting, now I finally understand what exactly the `sys_extract_utc()` function does. :-) – peter.hrasko.sk Oct 30 '14 at 10:39
No need to maintain two answers anymore, yours alone covers the question nicely. – peter.hrasko.sk Oct 30 '14 at 10:43
@nop77svk Thanks, but I think both our answers are just dublicates )) – Multisync Oct 30 '14 at 10:46
Yup, they are (duplicates to http://stackoverflow.com/questions/10554405/convert-unixtime-to-datetime-sql-oracle ), but one has to gain the reputation points somehow. ;-) – peter.hrasko.sk Oct 30 '14 at 10:49

Timestamp to number

1 Answers1