How to Convert Timestamp with Milliseconds to Date in Oracle

Using Oracle to_date function for date string with milliseconds

An Oracle DATE does not store times with more precision than a second. You cannot store millisecond precision data in a DATE column.

Your two options are to either truncate the string of the milliseconds before converting it into a DATE, i.e.

to_date( substr('23.12.2011 13:01:001', 1, 19), 'DD.MM.YYYY HH24:MI:SS' )

or to convert the string into a TIMESTAMP that does support millisecond precision

to_timestamp( '23.12.2011 13:01:001', 'DD.MM.YYYY HH24:MI:SSFF3' )

How to convert timestamp with milliseconds to date in Oracle

Unix Time

If you just need to convert from milliseconds since epoch to a timestamp in the UTC timezone, then:

SELECT TIMESTAMP '1970-01-01 00:00:00.000 UTC'
         + NUMTODSINTERVAL( 1483228800000 / 1000, 'SECOND' )
         AS TIME
FROM   DUAL

Which outputs:

TIME
2017-01-01 00:00:00.000 +00:00

Convert timestamp to date in Oracle SQL

CAST(timestamp_expression AS DATE)

For example, The query is : SELECT CAST(SYSTIMESTAMP AS DATE) FROM dual;

String to date in Oracle with milliseconds

Oracle stores only the fractions up to second in a DATE field.

Use TIMESTAMP instead:

SELECT  TO_TIMESTAMP('2004-09-30 23:53:48,140000000', 'YYYY-MM-DD HH24:MI:SS,FF9')
FROM    dual

, possibly casting it to a DATE then:

SELECT  CAST(TO_TIMESTAMP('2004-09-30 23:53:48,140000000', 'YYYY-MM-DD HH24:MI:SS,FF9') AS DATE)
FROM    dual

Convert epoch with milliseconds into Date format in PL/SQL

Please try this:

SELECT TO_TIMESTAMP('1970-01-01 00:00:00.0'
                   ,'YYYY-MM-DD HH24:MI:SS.FF'
       ) + NUMTODSINTERVAL(1493963084212/1000, 'SECOND')
FROM dual;

Or this if you want to return a string:

SELECT TO_CHAR( 
         TO_TIMESTAMP('1970-01-01 00:00:00.0'
                     ,'YYYY-MM-DD HH24:MI:SS.FF'
         ) + NUMTODSINTERVAL(1493963084212/1000, 'SECOND')
        ,'YYYY-MM-DD HH24:MI:SS.FF')
FROM dual;

Oracle: add milliseconds to my timestamp object

You add time to a timestamp by adding an interval. Intervals can go down to seconds only, but an interval of 0.001 seconds is a milliseconds interval which you can multiply with the desired value. E.g:

select systimestamp + interval '0.001' second * :millisecs from dual;

Oracle: add milliseconds to my timestamp object

You add time to a timestamp by adding an interval. Intervals can go down to seconds only, but an interval of 0.001 seconds is a milliseconds interval which you can multiply with the desired value. E.g:

select systimestamp + interval '0.001' second * :millisecs from dual;

---

Related Topics

Postgresql Does Not Use a Partial Index

Oracle Connect by Clause Equivalent in SQL Server

How to Generate (Or Get) a Ddl Script on an Existing Table in Oracle? I Have to Re-Create Them in Hive

The Argument 1 of the Xml Data Type Method "Value" Must Be a String Literal

SQL - Select Rows from Two Different Tables

Varchar to Decimal

SQL Find Difference Between Previous and Current Row

SQL Left Join Losing Rows After Filtering

Active Directory Data into SQL Table

How to Transform Rows to Columns

How to Label "Transitive Groups" with SQL

Postgresql Select Until Certain Total Amount Is Reached

In Postgres, Can You Set the Default Formatting for a Timestamp, by Session or Globally

Querying More Than 65536 Rows Error in Excel 2013

SQL Server 2005 Recursive Query with Loops in Data - Is It Possible

Sqlite - Replace Part of a String

Autoincrement Fields on Databases Without Autoincrement Field

How to Remove Duplicates from Space Separated List by Oracle Regexp_Replace

---

Leave a reply

Submit