CODEWITHSUNDEEP
sqloracle-database
Dc5nipe
Dc5nipe

Reputation: 119

Time stamp to something readable

I have this SQL Query that pulls exactly what I want but I am having trouble converting the time stamp to something readable. Here is what the Database outputs: enter image description here

The timestamp should be 8/21/19 1:56 PM

Here is the SQL Query I have right now: Can someone help me with this? Thank you!

select MACHINE_ID as ZUM_TOOL_GROUP, LAST_VALUE as MISTI, LAST_TIMESTAMP  
from machine_signal
where machine_id like 'WSI%' and signal_id = 'RT2_COUNT'
order by ZUM_TOOL_GROUP ASC

Updated Picture with the new code:

enter image description here

New Code:

select MACHINE_ID as ZUM_TOOL_GROUP, LAST_VALUE as MISTI,
(date '1970-01-01' + LAST_TIMESTAMP / (24 * 60 * 60 * 1000)) as time_date
from machine_signal
where machine_id like 'WSI%' and signal_id = 'RT2_COUNT'
order by ZUM_TOOL_GROUP ASC

The format works perfect but how do I get the timestamp correct? it should be 12:19 PM not 5:19PM

Upvotes: 0

Views: 71

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

This looks a Unix timestamp in milliseconds. Here is a simple conversion:

select date '1970-01-01' + 1566402391000 / (24 * 60 * 60 * 1000)
from dual;

If you want this in a particular timezone, you should offset it by the appropriate amount.

Upvotes: 3

Related Questions