Timestamp conversion

[skipizza]

How does one convert UTC timestamps to human readable form? I could not find a function to do this. Also, is there a function to calculate days between dates? Thanks!

 

[rcurva]

To convert a date into your desired format, you can use the TO_CHAR function.

SELECT TO_CHAR(sysdate,'MM-DD-YYYY')
FROM dual;

To get the no. of days between 2 dates, you can just subtract the two dates.

SELECT date1 - date2
FROM dual;
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[skipizza]

Yes, the
SELECT TO_DATE('31-SEP-02') - TO_DATE('01-SEP-01) FROM DUAL;
works fine. Thanks.

However, I could not get the UTC date, actually a UNIX timestamp based on standard UNIX epoch of 01/01/1970 at 00:00:00 GMT, to convert.

SELECT TO_CHAR(1001000000, 'MM-DD-YY') FROM DUAL;
(1001000000 in seconds is equivalent to 09/20/2001 at 15:33:20 GMT)

ERROR at line 1:
ORA-01861: literal does not match format string

I have not been able to find anything in Oracle to convert the UNIX timestamp to a CHAR, VARCHAR2 or DATE that reflects the conversion. However, there were some scripts that gives equivalent dates. I may have to create a table to list equivalent date, time for these timestamps or create some kind of code to convert this type of date.

 

[sem]

select to_date('01/01/1970 00:00:00','dd/mm/yyyy hh24:mi:ss') + <utc>/(24*60*60) from dual