Query epoch time using SQL Server to find date range

Question

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?

What do you mean the table's values use epoch time? What datatype is the column - is it not datetime? And what are some typical values? — Esoteric Screen Name, Jan 11 '16 at 20:24
Please be specific on what data type `epochdate` is. Is it a `BIGINT` containing the number of seconds since *january 1, 1970* or something like that? — TT., Jan 11 '16 at 21:03
Here's one solution, if you're able to create functions that you can query, assuming, as TT mentions above, you mean Unix Epoch Time: http://stackoverflow.com/questions/2904256/how-can-i-convert-bigint-unix-timestamp-to-datetime-in-sql-server — Jason Whitish, Jan 11 '16 at 21:20
here's an example of the value in the column. 1438005018. It's just an INT — Frantumn, Jan 12 '16 at 14:20

score 2 · Answer 1 · answered Jan 11 '16 at 21:35

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.

TT. · Answer 2 · 2016-01-12T15:29:12.413

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

Query epoch time using SQL Server to find date range

2 Answers2