SQL 如何从当前时间戳获取过去 24 小时？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28882878/
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
How to get last 24 hours from current time-stamp?
提问by moe
I am trying to pull all data for the last 24 hours but starting from the current time. If the current date-time is 5/3 and the time is 11:30 then i want to pull the last 24 hours from 11:30. The data type for date field is datetimeand it has only the date and time values without the seconds. Here is my current query
我试图提取过去 24 小时内的所有数据，但从当前时间开始。如果当前日期时间是 5/3 并且时间是 11:30，那么我想从 11:30 拉过去 24 小时。日期字段的数据类型是datetime，它只有日期和时间值，没有秒。这是我当前的查询
select Name, Location, myDate from myTable where myDate>= getdate()-24
the query above is giving me everything but i only want from the current time. this is how myDate look like in the table
上面的查询给了我一切，但我只想要当前时间。这就是表中 myDate 的样子
2015-03-05 10:30:00.000
2015-03-05 11:00:00.000
2015-03-05 11:30:00.000
2015-03-05 12:00:00.000
2015-03-05 12:30:00.000
2015-03-05 13:00:00.000
2015-03-05 13:30:00.000
2015-03-05 14:00:00.000
2015-03-05 14:30:00.000
回答by Mike Cole
To be more explicit with your intentions, you may want to write your query like so:
为了更明确地表达您的意图，您可能希望像这样编写查询：
select Name, Location, myDate from myTable where myDate>= DATEADD(hh, -24, GETDATE())
回答by Adam
I believe the issue is with:
我认为问题在于：
select Name, Location, myDate from myTable where myDate>= getdate()-24
The -24 as this would be -24 days
-24 因为这将是 -24 天
try:
尝试：
select Name, Location, myDate from myTable where myDate>= getdate()-1
An alternative would be to use the date add function:
另一种方法是使用日期添加功能：
http://www.w3schools.com/sql/func_dateadd.asp
http://www.w3schools.com/sql/func_dateadd.asp
DATEADD(datepart,number,date)
In your situation you could:
在您的情况下，您可以：
select Name, Location, myDate from myTable where myDate>= DATEPART (dd, -1, GETDATE())
Where we are adding negative one dd (days)
我们在哪里添加负一 dd（天）
回答by David Faber
Assuming you're using SQL Server 2012 or above, you could ANSI interval literals instead of dateadd():
假设您使用的是 SQL Server 2012 或更高版本，您可以使用 ANSI 间隔文字而不是dateadd()：
select Name, Location, myDate from myTable
where myDate>= getdate() - INTERVAL '24' HOUR;
If you want to be really ANSI standard, you can use CURRENT_TIMESTAMPinstead of getdate()(the two are equivalent).
如果你想成为真正的ANSI标准，你可以使用CURRENT_TIMESTAMP代替getdate()（两者是等价的）。
select Name, Location, myDate from myTable
where myDate >= CURRENT_TIMESTAMP - INTERVAL '24' HOUR;
