Oracle Varchar2 to Timestamp – How to Convert

date formatoracleoracle-11gtimestamp

In a oracle table column, there is a varchar2 column which contains value '2017-02-14T18:04:34.988+00:00' I need to convert it to timestamp. I am using TO_TIMESTAMP() to convert but I am not able to find out what date mask I need to provide. I have tried with

sql> select to_timestamp('2017-02-14T18:04:34.988+00:00','YYYY-MM-DD HH24:MI:SS.FF') from dual ;

But it does not work.

Kindly suggest what datetime mask I have to specify as 2nd argument.

Best Answer

According to ISO 8601 T is the time designator. You need to use a constant "T" in your date format mask.

And your time stamp contains timezone so you need to use TO_TIMESTAMP_TZ function to achieve this.

SQL> select to_timestamp_tz('2017-02-14T18:04:34.988+00:00',' YYYY-MM-DD"T"HH24:MI:SS.FF TZH:TZM') from dual;

TO_TIMESTAMP_TZ('2017-02-14T18:04:34.988+00:00','YYYY-MM-DD"T"HH24:MI:SS.FF
---------------------------------------------------------------------------
14-FEB-17 06.04.34.988000000 PM +00:00

Documentation:TO_TIMESTAMP_TZ

Related Solutions

Mysql – How to compare (in MySQL) a DATETIME value to a TIMESTAMP value

There is a manual page on Time and Date functions.

The best way to compare date and time and get minutes back is probably to use one of the following:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60
SELECT TIMESTAMPDIFF(MINUTE,timeb,timea)

Note that while TIMEDIFF works with times, TIMESTAMPDIFF requires a date component - and the values are switched around for TIMESTAMPDIFF.

You can return hours with either of these:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60/60
SELECT TIMESTAMPDIFF(HOUR,timeb,timea)

Note that when using DATEDIFF, the value returned is a difference between the date components of the given time - thus, a value of 23:59 on one day compared to a value of 0:01 on the next (a 2 minute difference) results in a date difference of 1 (a full day).

To get a difference between two times in 24-hour periods (length of a day) first calculate the difference in minutes then divide by the proper values to find the number of 24-hour periods. Try this:

SELECT TRUNCATE(TIMESTAMPDIFF(MINUTE,timeb,timea)/60.0/24.0,0)

This truncates the decimal portion, returning only the number of complete 24-hour periods in the specified timespan.

Convert a Unix timestamp to a DATETIME in a View

the result of the expression

to_date('1970-01-01','YYYY-MM-DD') + numtodsinterval(1244108886,'SECOND')

is a value of the oracle datatype DATE. This type contains date and time. If you want to display this value to a user you must convert it to a string. This conversion can be done by an application or you can let oracle convert it to a string type as it is done in this example. This automatic conversion to the oracle stringtype VARCHAR2 is controlled by nls_date_format and some server settings. If you want your view to return a VARCHAR2 value with the calculated date in the format 'YYYY-MM-DD HH24:MI:SS' and not a DATE value you can do this by using a conversion function: expression

to_char(to_date('1970-01-01','YYYY-MM-DD') + numtodsinterval(1244108886,'SECOND'),'YYYY-MM-DD HH24:MI:SS')

But in this case the return value of the view is a VARCHAR2 and not a DATE value.

Best Answer

Related Solutions

Mysql – How to compare (in MySQL) a DATETIME value to a TIMESTAMP value

Convert a Unix timestamp to a DATETIME in a View

Related Question