CODEWITHSUNDEEP
sql-serverepoch
Frantumn
Frantumn

Reputation: 1764

Query epoch time using SQL Server to find date range

I have to query an SQL Server database and the table's values use Epoch time (an int. Here's an example - 1438005018). I am wondering how I can write a query so that I can say the following...

select
  * 
from 
  tablename
where
  epochdate between 'yesterday at 12:00' and 'today at 12:00' --this is the part I'm not sure about.

Ideally, if it's easy, I'd like the query to use non-epoch logic as Epoch time confuses the crap out of me. Maybe there's a quick way of converting in SQL Server?

Upvotes: 1

Views: 3713

Answers (2)

TT.
TT.

Reputation: 16137

You query would look like the following:

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);

Upvotes: 1

Jason Whitish
Jason Whitish

Reputation: 1438

I posted a link above in the comments that may be a more practical solution if you're able to deploy functions in the database you're working with, but if you're only able to query, this is an option to try as well (this assumes SQL Server 2008 and above):

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

I used UTC above as a presumption of comparing based on UTC times, but you could also compare to your time zone, with the appropriate addition/subtraction of your time zone difference in seconds (e.g., add 18000 to each variable to get noon in Eastern Standard Time).

You can test your results by using http://www.epochconverter.com/ to compare your values in your variables.

Upvotes: 2

Related Questions