从日期转换为时代-Oracle [英] Convert from date to epoch-Oracle

问题描述

我需要将TextBox中的日期从日期转换为时代时间，以便可以将其插入Oracle DB.

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"

推荐答案

从值中减去DATE '1970-01-01'将得到天数(和小时/分钟/秒的分数)的差值，然后可以乘以24*60*60:

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

---

更新:

通常，纪元时间是从1970-01-01T00:00:00 UTC开始测量的.如果您的日期不是UTC，则需要转换时区.

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.

例如，如果您的日期具有时区Europe/Berlin:

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<>小提琴

这篇关于从日期转换为时代-Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！