带有日期的 Oracle 时间戳转换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14779610/
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
Oracle Timestamp Conversion with Dates
提问by NEW2WEB
Assuming this has a simple solution, but I can't find it.
假设这有一个简单的解决方案，但我找不到它。
I'm trying to do some logic on a DATE field in Oracle. My desire is to take a DATE field and subtract X hours from it.
我正在尝试对 Oracle 中的 DATE 字段执行一些逻辑。我的愿望是取一个 DATE 字段并从中减去 X 小时。
For instance: SELECT A.MyDATE - 100 Hours from dual;
例如： SELECT A.MyDATE - 100 Hours from dual;
however, I need a result in a timestamp format 'YYYY-MM-DD hh:mm'.
但是，我需要时间戳格式为“YYYY-MM-DD hh:mm”的结果。
I've tried CAST(A.MyDATE as TIMESTAMP) - NUMTODSINTERVAL(100/24,'day')however it didn't work.
我试过了，CAST(A.MyDATE as TIMESTAMP) - NUMTODSINTERVAL(100/24,'day')但是没有用。
I found out that the issue is that the MyDATE field when cast to a timestamp still contained some residual time elements. How can I reset these??
我发现问题在于 MyDATE 字段在转换为时间戳时仍然包含一些剩余时间元素。我该如何重置这些？？
Thanks!
谢谢！
回答by Gordon Linoff
You can just do this with subtraction:
你可以用减法来做到这一点：
select a.MyDate - 100.0/24
To convert to varchar:
要转换为 varchar：
select to_char(a.MyDate - 100.0/24, 'YYYY-MM-DD')
And, if you want to get rid of that pesky time on the date:
而且，如果你想摆脱约会中那个讨厌的时间：
select trunc(a.MyDate - 100.0/24) as JustTheDate
回答by Art
The formats and dates in my example can be changed to any other formats and dates:
我的示例中的格式和日期可以更改为任何其他格式和日期：
SELECT To_Timestamp(To_Char(Sysdate - INTERVAL '100' HOUR, 'MM/DD/YYYY HH24:MI'), 'MM/DD/YYYY HH24:MI')
FROM dual
/
Output:
2/4/2013 10:18:00.000000000 AM
To remove time element add Trunc() to any of your dates...:
SELECT Trunc(To_Timestamp(To_Char(Sysdate - INTERVAL '100' HOUR, 'MM/DD/YYYY HH24:MI'), 'MM/DD/YYYY HH24:MI'))
FROM dual
/
Output: 2/4/2013
Conversion/Casting - when using other dates in place of sysdate then add formats as in my other examples:
SELECT CAST(SYSDATE AS TIMESTAMP) - INTERVAL '100' HOUR FROM dual
/
Output: 2/4/2013 10:26:35.000000000 AM
SELECT start_date tstamp_to_date, CAST(start_date AS timestamp) date_to_tstamp FROM
(
SELECT to_date(to_char(to_timestamp ('2013-02-07 10:07:47.000' , 'YYYY-MM-DD HH24:MI:SS.FF'),'DD-MON-YYYY HH24:MI:SS'), 'DD-MON-YYYY HH24:MI:SS') start_date
FROM dual
)
/
Output:
tstamp_to_date date_to_tstamp
-------------------------------------------------------
2/7/2013 10:07:47 AM 2/7/2013 10:07:47.000000 AM
回答by Justin Cave
In Oracle, a DATEalways has a day and a time component. Depending on the tool you are using and your session's NLS_DATE_FORMAT, it is entirely possible that the tool may not display the time component when you look at the data. But that is simply a display question, it has no impact on the actual data.
在 Oracle 中，aDATE始终具有日期和时间组件。根据您使用的工具和会话的NLS_DATE_FORMAT，当您查看数据时，该工具完全有可能不显示时间组件。但这只是一个显示问题，它对实际数据没有影响。
If you want to subtract 100 hours from midnight on the day that MyDaterepresents
如果你想从MyDate代表的那一天的午夜减去 100 小时
SELECT TRUNC(MyDate) - interval '100' hour
FROM dual
This will return a DATE. If you want to return a string in a particular format
这将返回一个DATE. 如果要返回特定格式的字符串
SELECT TO_CHAR( TRUNC(MyDate) - interval '100' hour, 'YYYY-MM-DD hh:mi am' )
FROM dual
Note that I'm assuming that there was a typo in your question. I assume that you want to display the minutes after the hour (mi) rather than the month (mm).
请注意，我假设您的问题中有错字。我假设您想在小时 ( mi)之后显示分钟而不是月份 ( mm)。
回答by Raj
I am trying to fetch the records which is older than 30 days (from Mod_date) and I am using the below query and it is returning all the data and I want only 30 days old data.
我正在尝试获取超过 30 天的记录（来自 Mod_date），我正在使用以下查询，它正在返回所有数据，而我只想要 30 天的旧数据。
Sample :- Mod_date 03-NOV-12 12.00.00.000000000 AM
示例：- Mod_date 03-NOV-12 12.00.00.000000000 AM
Query :-
询问 ：-
select Mod_date from fil_cnfact where Mod_date <= sysdate -30 order by Mod_date asc ;
select Mod_date from fil_cnfact where Mod_date <= sysdate -30 order by Mod_date asc ;
