我必须查询SQL Server数据库,并且表的值使用Epoch时间(int。下面是一个示例-1438000518)。我想知道如何编写查询,以便说出以下内容。。。
select
*
from
tablename
where
epochdate between 'yesterday at 12:00' and 'today at 12:00' --this is the part I'm not sure about.
理想情况下,如果简单的话,我希望查询使用非epoch逻辑,因为epoch时间会让我感到困惑。也许有一种在SQL Server中快速转换的方法?
我在上面的评论中发布了一个链接,如果您能够在使用的数据库中部署函数,这可能是一个更实用的解决方案,但如果您只能查询,这也是一个尝试的选项(假设SQL Server 2008及更高版本):
declare @todayepoch bigint, @yesterdayepoch bigint;
select @todayepoch =
cast((cast(dateadd(hour, 12,
cast(cast(sysutcdatetime() as date) as datetime)) as decimal(24,10))
- cast(cast('1970-01-01' as datetime) as decimal(24,10)))
*60.0*60.0*24.0 as int), -- + 18000, --Eastern time
@yesterdayepoch =
cast((cast(dateadd(hour, -12,
cast(cast(sysutcdatetime() as date) as datetime)) as decimal(24,10))
- cast(cast('1970-01-01' as datetime) as decimal(24,10)))
*60.0*60.0*24.0 as int) -- + 18000 --Eastern time
select @todayepoch, @yesterdayepoch
select
*
from
tablename
where
epochdate between @yesterdayepoch and @todayepoch
我使用上面的UTC作为基于UTC时间进行比较的假设,但您也可以与您的时区进行比较,并适当地加/减您的时区差异(以秒为单位)(例如,将18000添加到每个变量中,以获得东部标准时间的中午)。
您可以使用http://www.epochconverter.com/比较变量中的值。
您的查询如下所示:
DECLARE @dt_from DATETIME;
DECLARE @dt_to DATETIME;
SELECT
@dt_from=DATEADD(HH,-12,CAST(FLOOR(CAST(GETUTCDATE() AS FLOAT)) AS DATETIME)), -- strip time of current UTC date/time, and subtract 12 hrs
@dt_to=DATEADD(HH,+12,CAST(FLOOR(CAST(GETUTCDATE() AS FLOAT)) AS DATETIME)); -- strip time of current UTC date/time, and add 12 hrs
SELECT
*
FROM
tablename
WHERE
epochdate BETWEEN DATEDIFF(s,'1970-01-01',@dt_from) AND DATEDIFF(s,'1970-01-01',@dt_to);