SQL Server to SYnapse SQL syntax modifications

Question

I have a query that works just fine in SQL Server but I would like some help with the required syntax modifications to make it work at Synapse SQL. Any help would be appreciated! The id's are unix timestamps. I want to count the id's of the previous day filtering by time range using the unix timestamps

SELECT COUNT(Id)
FROM [dbo].[name]
WHERE Id >= CONVERT(bigint, DATEDIFF(SECOND,'1970-01-01', CONVERT(date,dateadd(d, -1, GETDATE()))))*1000 
          and Id < CONVERT(bigint, DATEDIFF(SECOND,'1970-01-01', CONVERT(date,GETDATE())))*1000

Answer 1

When SQL servers and Azure Synapse are in different time zones, the results of Query will be varying. Azure Synapse will be in UTC time zone.

In order to convert current date value to local datetime value in azure synapse, User defined function is created as in below script.

Create  FUNCTION [dbo].[CurrentLocalTime]( @TimeZoneName sysname )
RETURNS datetime
AS
BEGIN
RETURN DATEADD(hour, 
                 TRY_CAST((SELECT REPLACE(current_utc_offset, N':', N'.')
                           FROM sys.time_zone_info
                           WHERE [name] = @TimeZoneName) AS decimal(18,2)), 
                 SYSDATETIME());
END;

Instead of using getdate() of function, the above function is used in the query.

Query

SELECT COUNT(Id) FROM sample1
WHERE Id >= CONVERT(bigint, DATEDIFF(SECOND,'1970-01-01', CONVERT(date,dateadd(d, -1, dbo.CurrentLocalTime('AUS Eastern Standard Time' )))))*1000 
and Id < CONVERT(bigint, DATEDIFF(SECOND,'1970-01-01', CONVERT(date,dbo.CurrentLocalTime('AUS Eastern Standard Time' ))))*1000

The time zone can be replaced in the query as per the requirement.

Results for Sample data: