Re: Convert Epoch (in milliseconds) to Date Format

tereglow schrieb:
Hello all,

Bear with me here, I'm a Unix Admin., not a DBA!

I have a query that returns a field with its corresponding timestamp
of when it was created. The timestamp is returned in epoch format,
measured in milliseconds. Is there a way to convert this into a
normal date within Oracle?

For example:

SELECT server, createtime
FROM servers;

This gives me:

server1 | 1171391881904

I need the epoch in milliseconds to show a human readable date. I.e.
something like YYYY-MM-DD hh:mm:ss or the like.

I appreciate any help,
Tom


SELECT server, createtime,DATE '1970-01-01' + createtime/(1000*60*60*24)
FROM servers

?

Best regards

Maxim
.

Relevant Pages

  • Convert Epoch (in milliseconds) to Date Format
    ... I have a query that returns a field with its corresponding timestamp ... The timestamp is returned in epoch format, ... SELECT server, createtime ... I need the epoch in milliseconds to show a human readable date. ...
    (comp.databases.oracle.server)
  • Re: Migration of Teradata and DB2 Timestamp fields.
    ... There is no datatype in SQL Server that has a precision of 1 millisecond. ... - Store the timestamp in a CHARcolumn in format ... Note that the precision will go back to 3.33 milliseconds when you ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: Convert java.util.Date to java.sql.Date
    ... Review my recommendation about java.text.DateFormat. ... Aside from its more natural match to the SQL TIMESTAMP type, Timestamp holds time to nanosecond resolution. ... Like its parent, java.util.Date, Timestamp holds long values that represent milliseconds and nanoseconds since epoch. ...
    (comp.lang.java.programmer)
  • Re: Extracting milliseconds from a time field
    ... component of the timestamp, eg in this case 679 in cell B. ... NOWis indeed milliseconds, not less. ... limited precision arithmetic when applied to decimal values. ...
    (microsoft.public.excel)
  • Re: Generating serial numbers
    ... need to allocate unique names to them. ... distinguish between transactions that arrive a few tens of milliseconds ... So the timestamp would be quite long. ... (such as base64, as made simple with the Math::BaseCnv module that Uri ...
    (comp.lang.perl.misc)