sql - Convert from date to epoch-Oracle

Question

I need to convert a date from a TextBox from date to epoch time so that I can insert it into Oracle DB.

I managed to convert from epoch to date as below, but couldn't find a way to convert it the other way.

SelectCommand="SELECT ID,
            COMPANY,
            FIRST_NAME,
            LAST_NAME,
            ID_NUMBER,
            (SELECT TO_CHAR(TO_DATE('01-JAN-1970','DD/MM/YYYY')
            +(TRAINING_DATE/60/60/24), 'MM/DD/YYYY') FROM dual) AS TRAINING_DATE,
            (SELECT TO_CHAR(TO_DATE('01-JAN-1970','DD/MM/YYYY')
            +(TRAINING_VALABILITY/60/60/24),'MM/DD/YYYY') FROM dual) AS TRAINING_VALABILITY
    FROM CONTRACTORS
    ORDER BY COMPANY"

Answer 1

Subtracting DATE '1970-01-01' from the value will give the number of days (and fractional hours/minutes/seconds) difference and then you can multiply by 24*60*60:

(date_value - DATE '1970-01-01')*24*60*60

---

Update:

Typically, epoch time is measured from 1970-01-01T00:00:00 UTC. If your date is not in UTC then you will need to convert time zones.

For example, if your date has the time zone Europe/Berlin:

( CAST(
    FROM_TZ(
      CAST( date_value AS TIMESTAMP ),     -- Cast to timestamp
      'Europe/Berlin'                      -- Convert to expected Time Zone
    )
    AT TIME ZONE 'UTC'                     -- Convert Time Zone to UTC
    AS DATE                                -- Cast back to DATE data type
  )
  - DATE '1970-01-01'
)*24*60*60

db<>fiddle