SQL Oracle：如何在午夜之前选择当前日期（今天）？

Oracle: How to select current date (Today) before midnight?

提问by gaboroncancio

Using Oracle, how do you select current date (i.e. SYSDATE) at 11:59:59?

使用Oracle，如何在11:59:59 选择当前日期（即SYSDATE）？

Take into account that the definition of midnight might be a little ambiguous(Midnight Thursday means Straddling Thursday and Friday or Straddling Wednesday and Thursday?).

考虑到午夜的定义可能有点模棱两可（星期四午夜意味着跨越周四和周五还是跨越周三和周四？）。

回答by gaboroncancio

To select current date (Today) before midnight (one second before) you can use any of the following statements:

要在午夜（前一秒）之前选择当前日期（今天），您可以使用以下任何语句：

SELECT TRUNC(SYSDATE + 1) - 1/(24*60*60) FROM DUAL
SELECT TRUNC(SYSDATE + 1) - INTERVAL '1' SECOND FROM DUAL;

What it does:

它能做什么：

1. Sum one day to SYSDATE: SYSDATE + 1, now the date is Tomorrow
2. Remove time part of the date with TRUNC, now the date is Tomorrow at 00:00
3. Subtract one second from the date: - 1/(24*60*60)or - INTERVAL '1' SECOND FROM DUAL, now the date is Today at 11:59:59

1. 总和一天到SYSDATE: SYSDATE + 1，现在日期是明天
2. 用 删除日期的时间部分TRUNC，现在日期是 Tomorrow at 00:00
3. 从日期减去一秒：- 1/(24*60*60)或者- INTERVAL '1' SECOND FROM DUAL，现在日期是 Today at 11:59:59

Note 1:If you want to check date intervals you might want to check @Allan answer below.

注意 1：如果您想检查日期间隔，您可能需要检查下面的@Allan 答案。

Note 2:As an alternative you can use this other one (which is easier to read):

注意 2：作为替代，您可以使用另一个（更易于阅读）：

SELECT TRUNC(SYSDATE) + INTERVAL '23:59:59'  HOUR TO SECOND FROM DUAL;

1. Remove time part of the current date with TRUNC, now the date is Today at 00:00
2. Add a time interval of 23:59:59, now the date is Today at 11:59:59

1. 用 删除当前日期的时间部分TRUNC，现在日期是今天 00:00
2. 添加时间间隔23:59:59，现在日期是今天 11:59:59

Note 3:To check the results you might want to add format:

注意 3：要检查结果，您可能需要添加格式：

SELECT TO_CHAR(TRUNC(SYSDATE + 1) - 1/(24*60*60),'yyyy/mm/dd hh24:mi:ss') FROM DUAL
SELECT TO_CHAR(TRUNC(SYSDATE + 1) - INTERVAL '1' SECOND,'yyyy/mm/dd hh24:mi:ss') FROM DUAL
SELECT TO_CHAR(TRUNC(SYSDATE) + INTERVAL '23:59:59','yyyy/mm/dd hh24:mi:ss') FROM DUAL

回答by Allan

Personally, I dislike using one second before midnight. Among other things, if you're using a timestamp, there's a possibility that the value you're comparing to falls between the gaps (i.e. 23:59:59.1). Since this kind of logic is typically used as a boundary for a range condition, I'd suggest using "less than midnight", rather than "less than or equal to one second before midnight" if at all possible. The syntax for this simplifies as well. For instance, to get a time range that represents "today", you could use either of the following:

就个人而言，我不喜欢在午夜前使用一秒钟。除此之外，如果您使用的是timestamp，则您要比较的值可能会落在间隙之间（即 23:59:59.1）。由于这种逻辑通常用作范围条件的边界，因此我建议尽可能使用“小于午夜”，而不是“小于或等于午夜前一秒”。其语法也简化了。例如，要获得代表“今天”的时间范围，您可以使用以下任一方法：

date_value >= trunc(sysdate) and date_value < trunc(sysdate) + 1
date_value >= trunc(sysdate) and date_value < trunc(sysdate) + interval '1' day

It's a little more cumbersome than using between, but it ensures that you never have a value that falls outside of the range you're considering.

它比使用 麻烦一点between，但它确保您永远不会有超出您考虑的范围的值。

回答by Sylvain Leroux

The real ambiguity is probably with leap secondsand maybe daylight saving (but I don't know if there is some case where it changes at midnight or not).

真正的歧义可能是闰秒，也可能是夏令时（但我不知道是否在某些情况下它会在午夜发生变化）。

Anyway, for the usual case, there are few solutions:

无论如何，对于通常的情况，有几个解决方案：

-- all of these will produce a `DATE` result:
SELECT TRUNC(SYSDATE+1)-1/86400 FROM DUAL;
SELECT TRUNC(SYSDATE+1) - INTERVAL '1' SECOND FROM DUAL;
SELECT TRUNC(SYSDATE) + INTERVAL '23:59:59'  HOUR TO SECOND FROM DUAL;

---

---

Some little oddities if you use timestamps though:

但是，如果您使用时间戳，则有些奇怪：

• TRUNCwill silently convert the value to DATE;
• Adding/subtracting a NUMBERto/from a TIMESTAMPwill produce DATEtoo. See Datetime/Interval Arithmetic for the details.

• TRUNC将默默地将值转换为DATE;
• 添加/减去一个NUMBER到/从一个TIMESTAMP也会产生DATE。有关详细信息，请参阅日期时间/间隔算术。

-- those two will produce a `DATE` *too*, not a `TIMESTAMP`:
SELECT TRUNC(SYSTIMESTAMP) + INTERVAL '23:59:59'  HOUR TO SECOND FROM DUAL;

SELECT TO_TIMESTAMP(TRUNC(SYSTIMESTAMP+1))-1/86400 FROM DUAL;