2

I have the following timestamp "1505143211567". By removing the last 3 numbers I can able to convert to normal human readable format. The DB is running from Oracle. I want to convert this timestamp to human readable. I tried something like below.

From sql I can write it by removing last 3 and 

select from_unixtime(1505143211)

But in oracle its kind of different. And each time I dont want to remove the number and check manually.

Need is : DB query.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786

1 Answers1

1

The timestamp you are getting seems to be an EPOC value, when using an online converter I got the following:

1505143211567 -> 2017-09-11 15:20:12

I'm no expert in Oracle, however there are a lot of resources out there in converting this to a human readable format, does the following help ? 

select TO_CHAR( FROM_TZ( CAST(DATE '1970-01-01' + (1/24/60/60/1000) * <column name> AS TIMESTAMP), 'Europe/London'), 'MM/DD/YYYY HH24:MI:SS') from <tablename>;

Where column name is your timestamp column, and tablename is obviously the table you are getting the column from. source can be found here.

You also have a few stackoverflow posts with the issue as well. Here might be a good one to follow.

Richard G
  • 269
  • 2
  • 12