从日期转换为纪元-Oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38055178/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert from date to epoch-Oracle
提问by Janine
I need to convert a date from a TextBox from date to epoch time so that I can insert it into Oracle DB.
我需要将 TextBox 中的日期从日期转换为纪元时间，以便将其插入 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"
回答by MT0
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 '1970-01-01'从该值中减去将给出天数（和小数小时/分钟/秒）差，然后您可以乘以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.
通常，纪元时间是从 测量的1970-01-01T00:00:00 UTC。如果您的日期不是 UTC，那么您将需要转换时区。
For example, if your date has the time zone Europe/Berlin:
例如，如果您的日期具有时区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
回答by Janine
UpdateCommand="UPDATE CONTRACTORS
SET COMPANY=:COMPANY,
FIRST_NAME=:FIRST_NAME,
LAST_NAME=:LAST_NAME,
ID_NUMBER=:ID_NUMBER,
TRAINING_DATE=(TO_DATE(:TRAINING_DATE, 'MM-DD-YYYY HH24:MI:SS') - TO_DATE('01-JAN-1970','DD/MM/YYYY'))*24*60*60,
TRAINING_VALABILITY=(TO_DATE(:TRAINING_VALABILITY, 'MM-DD-YY`enter code here`YY HH24:MI:SS') - TO_DATE('01-JAN-1970','DD/MM/YYYY'))*24*60*60
WHERE (ID=:ID)"
