Getting the Date('YYYY-MM-DD') from a text representation of a timestamp in milliseconds in Oracle

Asked Jun 21 '16 at 17:17

Active Jun 21 '16 at 17:29

Viewed 41 times

We have a DB that is storing the timestamp (in milliseconds since Unix Epoch, Jan 1st 1970) in a text column in a Oracle DB. We would like to get a Date('YYYY-MM-DD') from that

I tried the following but it doesn't seem to work:

 TO_CHAR(cast(trunc(TO_NUMBER(created_on)/1000),'YYYY-MM-DD') as timestamp)

edited Jun 21 '16 at 17:29

sstan

35,425
6
48
66

asked Jun 21 '16 at 17:17

Saqib Ali

3,953
10
55
100

Can you provide more details about this field? Is it the number of milliseconds since a specific point in time? Otherwise, I don't see how it can be translated to a specific date. – sstan Jun 21 '16 at 17:22
Milleseconds from beginning of time? – Marcus Höglund Jun 21 '16 at 17:23
It is the unix/epoch timestamp just in milliseconds. – Saqib Ali Jun 21 '16 at 17:25

Getting the Date('YYYY-MM-DD') from a text representation of a timestamp in milliseconds in Oracle

0 Answers0