How to get millis of timestamp since 1970 UTC in Oracle SQL? [duplicate]

Question

I have a table which one of the columns is of type TIMESTAMP(6).

How do I get the milliseconds of the column's data since 1970 UTC?

Answer 1

Try this

select (cast(your_column as date) - date '1970-01-01')*24*60*60 from your_table;

Answer 2

select 
  MY_TS as orig
  , extract(day from (MY_TS-to_timestamp_tz('01-01-70 0', 'DD-MM-RR TZH')))*1000*60*60*24
  + extract(hour from (MY_TS-to_timestamp_tz('01-01-70 0', 'DD-MM-RR TZH')))*1000*60*60
  + extract(minute from (MY_TS-to_timestamp_tz('01-01-70 0', 'DD-MM-RR TZH')))*1000*60
  + extract(second from (MY_TS-to_timestamp_tz('01-01-70 0', 'DD-MM-RR TZH')))*1000
    as millis
from MY_TABLE;