0

I am running a query on an oracle db and am trying to get the start time for this particular event. The date is just stored as a number in the table and the only way I have been able to retrieve it is with TO_CHAR(TIMEFRAME_START). This gives me an output of 1574402400.

I know that this should correspond to 11/21/2019 11:00:00 PM MST based on a different app that pulls the same information but I have no idea how it does this conversion. I need to retrieve additional rows and need to be able to convert that output to a standard date format.

Does anyone have any ideas on how I can accurately format the original output?

Boneist
  • 22,910
  • 1
  • 25
  • 40
gearhead28
  • 11
  • 3

2 Answers2

0

Assuming that the number represents a unix timestamp, you can turn it to a date with the following expression:

to_date('1970-01-01', 'yyyy-mm-dd') + timeframe_start / 60 /60 / 24

Rationale:

  • unix epoch starts on January 1st, 1970

  • in Oracle, you can add a fractional number of days to a date

So basically we would convert the epoch timestamp to a number of days by dividing it by 60 (seconds per minutes), 60 (minutes per hour), and 24 (hours per day), and then add it to the date that represents epoch start.

You can then format the resulting date to the desired string format with to_char():

to_char(
    to_date('1970-01-01', 'yyyy-mm-dd') + timeframe_start / 60 /60 / 24,
    'mm/dd/yyyy hh12:mi:ss am'
)
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Thank you for the quick response! This worked perfectly. Now i just need to convert from UTC to local time. Can this be integrated into this function or should I handle that in a method once the date is returned? – gearhead28 Nov 22 '19 at 13:04
0

You should do the following since that is a UNIX timestamp:

TO_CHAR(TO_DATE('19700101000000','YYYYMMDDHH24MISS') + numtodsinterval(TO_CHAR(TIMEFRAME_START), 'SECOND'), 'MM/DD/YYYY HH:MI:SS AM')

Ergi Nushi
  • 837
  • 1
  • 6
  • 17