来自双表的SQL日期和时间比较 [英] Sql date and time comparison from dual table
问题描述
我有两列state_date和state_time
I have two columns state_date and state_time
我想使用列state_date和state_time从数据库中提取记录.state_date和state_time应该小于或等于sql以下.在sql下给出日期和时间.但是我有单独的一列是具有日期而另一列是有时间.SO首先我想比较日期，然后再比较时间.如何修改下面的查询以分别以毫秒为单位获取日期和时间.
I want to pull records from database using columns state_date and state_time.State_date and state_time are should less than or equals to below sql.Below sql giving date and time.But i have individual columns one is having date and other is having time.SO first i want to compare date and after that time.How can i modify below query to get date and time seperately with milli seconds.
select decode(TRIM(to_char(SYSDATE,'Day')),'Monday','3','1') from dual
样本数据:
订单表:
order id order name order date
1 pizza 20-feb-2012
2 burger 17-feb-2012
order_state表
order_state table
order id order_state order_date order_time
1 initiated 20-feb-2012 12:29:11:203
1 processed 21-feb-2012 12:29:12:112
1 cancelled 21-feb-2012 12:29:11:311
所以在这里我想获取最新记录，即根据order_date和order_time，order_id和今天date取消.如果我今天查询的时间是12:29:10:311或12:29:11:310.我想获取已取消的记录.
So here i want to fetch the latest record i.e cancelled based on order_date and order_time ,order_id and today date.if i query today at 12:29:10:311 or 12:29:11:310.i want to fetch the cancelled record.
谢谢
Chaitu
推荐答案
好的，我想我已经有了你.您要执行以下操作吗?
Okay, I think I've got you. You want to do the following?
select <columns>
from my_table
where state_date <= <some date>
and state_time <= <some time>
关心毫秒是很不寻常的，但是如果您这样做，则应该使用systimestamp.
It's fairly unusual to care about the milliseconds but if you do then you should use systimestamp.
从日期和时间划分的事实来看，这些都是字符，因此，我不得不猜测
Judging by the fact you've split date and time, these are characters, So, I'm going to have to guess the format masks. If they're wrong the link should guide you on what to do. It's not wise, by the way, to split a date in this way. You could create a column using the timestamp data-type in your table, which would make your problem extremely simple.
因此，我不知道您为什么选择查询的'Day'格式，但与此相关的<some date>却变成了to_char(sysdate, 'DAY').
So, I don't know why you've chosen the 'Day' format for your query but going with that <some date> becomes to_char(sysdate, 'DAY').
根据您在<some date>以下的评论，将为to_char(sysdate, 'DD-MON-YY')
From your comment below <some date> would be to_char(sysdate, 'DD-MON-YY')
<some time>将是to_char(systimestamp,'HH24:MI:SS:FF3')，这将使您的时间戳达到毫秒，尽管数据类型可以达到微秒.
<some time> would be to_char(systimestamp,'HH24:MI:SS:FF3'), which would give you the timestamp to the millisecond, though the datatype can go to the micro-second.
对我来说似乎有些奇怪，但是您的查询将变为:
It seems a little strange to me but your query would then become:
select <columns>
from my_table
where state_date <= to_char(sysdate, 'DD-MON-YY')
and state_time <= to_char(systimestamp,'HH24:MI:SS:FF3')
如果将日期存储为字符串，将其以yyyymmdd格式存储会更加正常，因此至少可以保证它是正确的.如果您已完成上述操作，则只需更改格式掩码即可.如果您还没有，那么这些查询将无法按预期工作.
It would be more normal, if storing a date as a string, to store it in the format yyyymmdd so at least you can guarantee it's in order. If you've done something like this then just change the format mask. If you haven't then these queries aren't going to work as intended.
个人而言，如果您必须以这种方式存储数据，并假设state_date存储为例如dd-mon-yy，即包括年，月，日，而state_time存储为如上所示，那么我会做这样的事情:
Personally, if you have to store data this way and assuming state_date is stored as, say, dd-mon-yy, i.e. including year, month AND day, and state_time is stored as indicated above, then I would do something like this:
select <columns>
from my_table
where to_timestamp(state_date || state_time, 'DD-MON-YYHH24:MI:SS:FF3')
<= systimestamp
这使得发生的事情更加明显，并且在这种情况下<的含义也没有歧义，因为日期始终小于将来的日期，这不一定适用于字符串.
It makes it a lot more obvious what is going on and there is no ambiguity on what < means in this situation as a date will always be less than a future date, which doesn't necessarily hold true for strings.
我希望这是有道理的.
这篇关于来自双表的SQL日期和时间比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
