DateTime在哪里Clause Oracle [英] DateTime on Where Clause Oracle
问题描述
似乎有很多查询语法来获取oracle数据库中的数据,在这里我只想询问一下正常工作的查询,但是我根本无法理解。查询是:
it seems there are lot of query syntax to fetch data on oracle database, here I just want to ask about the query that works fine but I cant understand at all. The query is :
Select
....
From
...
Where
TO_CHAR(TO_DATE('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS') +
(create_date / ( 60 * 60 * 24 )),
'MM/DD/YY HH24:MI:SS') = '06/30/14 21:41:11'
;
从上面的查询可以很好的工作。但是我不明白为什么 TO_DATE('01 / 01/1970 00:00:00','MM / DD / YYYY HH24:MI:SS 和(create_date /(60 * 60
From the query above it's work fine. But I cant understand why there's TO_DATE('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS and (create_date / ( 60 * 60 * 24 )), 'MM/DD/YY HH24:MI:SS')
在 create_date 中,
'MM / DD / YY HH24:MI:SS' >字段它显示unix datetime,如1404164471
on the create_date fields it show unix datetime such as 1404164471
有人可以解释这个吗?
提前感谢
thanks in advance
推荐答案
TO_DATE('01 / 01/1970 00:00:00','MM / DD / YYYY HH24:MI:SS')
将特定格式(第二个参数)的字符串(第一个参数)转换为日期。
TO_DATE('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
converts a string (first argument) in certain format (second argument) to a date.
(create_date /(60 * 60 * 24))
create_date 包含秒,此表达式将它们转换为天数(1分= 60秒,1小时= 60分钟,1天= 24小时=> 60 * 60 * 24 =一天中的秒数)。当您添加一个日期时,Oracle认为此数字包含几天,这就是您需要这样的对话的原因。
(create_date / ( 60 * 60 * 24 ))
create_date contains seconds, this expression converts them into the number of days (1 minute = 60 seconds, 1 hour = 60 minutes, 1 day = 24 hours => 60*60*24 = the number of seconds in a day). When you add a number to a date Oracle thinks that this number contains days that's why you need such a conversation.
TO_DATE('01 / 01 / 1970 00:00:00','MM / DD / YYYY HH24:MI:SS')+(create_date /(60 * 60 * 24))
给你一个存储在create_date中的日期传统格式
TO_DATE('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS') + (create_date / ( 60 * 60 * 24 ))
gives you a date stored in create_date but in "traditional" format
看来,您需要将unix时间与日期进行比较。最好使用这个条件:
It seems you need to compare unix time with date. It would be better to use this condition:
Select
....
From
...
Where create_date = trunc( (TO_DATE('06/30/14 21:41:11', 'MM/DD/YY HH24:MI:SS')
- TO_DATE('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
) * 24 * 60 * 60
);
这篇关于DateTime在哪里Clause Oracle的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!