0

Using SQL Server 2016, I have a table that contains a column DateTime of type INT which contains a UNIX timestamp. So if I want to get all the rows where the timestamp is yesterday, I know I need to check if the timestamp is >= the timestamp for 'yesterday at 00:00' and <= 'yesterday at 23:59'.

How can I do this with SQL Server 2016?

Found a few examples but they where for MySQL. I did find this example below. But it throws an error, so I'm probably not using it correctly. Or actually, this might be MySQL as well...

WHERE [DateTime] <= DATEADD(day, -1, convert(day, GETDATE()))

Any help would be greatly appreciated!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Matt
  • 11
  • 3
  • 1
    SQL Server has a native `DateTime` type, so a) use that instead of `int` for your timestamps if possible; and b) break your problem down by finding answers to the two questions ["How do I convert Unix timestamp to SQL Server `DateTime`?"](https://stackoverflow.com/questions/2904256/how-can-i-convert-bigint-unix-timestamp-to-datetime-in-sql-server) and "How do I get "yesterday" from a `DateTime` value?" – IMSoP Aug 05 '20 at 18:26
  • Thank you for pointing me in the right direction. Very much appreciated. Adding my result in the answers below. – Matt Aug 05 '20 at 19:19

1 Answers1

0

Here's my solution that seems to work. Did a test manually where I checked if the timestamp was between 1596513600 and 1596599940, and got the same output from the Solution below:

Function to Convert Timestamp to datetime type (*adjusts for Local time):

CREATE FUNCTION dbo.fn_ConvertToDateTime (@Datetime int)
RETURNS DATETIME
AS
BEGIN
    DECLARE @LocalTimeOffset BIGINT
           ,@AdjustedLocalDatetime BIGINT;
    SET @LocalTimeOffset = DATEDIFF(second,GETDATE(),GETUTCDATE())
    SET @AdjustedLocalDatetime = @Datetime - @LocalTimeOffset
    RETURN (SELECT DATEADD(second,@AdjustedLocalDatetime, CAST('1970-01-01 00:00:00' AS datetime)))
END;
GO

SQL Statement:

SELECT *
FROM dbname.dbo.Tablename
WHERE dbo.fn_ConvertToDateTime([DateTime]) >= dateadd(day,datediff(day,1,GETDATE()),0)
    AND dbo.fn_ConvertToDateTime([DateTime]) < dateadd(day,datediff(day,0,GETDATE()),0)

The Resulting output shows only Rows that contain a unix timestamp from Yesterday.

Matt
  • 11
  • 3