How Do I Properly Offset GETDATE() in Marketing Cloud Query Activities for US Eastern Time Year-Round?

Question

I want to use a Query Activity within Automation Studio to find all of our app's users who last logged in between 24 and 25 hours ago. When I run the below query into a data extension, I instead get LastLogin (a datetime field that, as the name implies, records our users' last login) values from 26-27 hours ago.

I'm in New York (US Eastern Daylight Time or UTC-4 right now). This seems to imply Salesforce's servers are set up in Mountain Daylight Time. But I'm not sure if maybe instead the servers reside in some place like California and just use a UTC offset (making it seem like it's in Mountain Time for half the year and Pacific Time for the other half).

Does anyone have experience with GETDATE() in Marketing Cloud or knows where I can find documentation from Salesforce on the subject? I'd like to know what the best practice is here so I don't have to change all my queries twice a year to accomodate for Daylight Savings.

SELECT u.UserId, u.EmailAddress, p.LastLogin
FROM ENT.[User] u
INNER JOIN Player p
ON u.UserId = p.UserId
AND u.UserId IN
    (SELECT UserId
    FROM Player
    WHERE LastLogin <= DATEADD(hh, -24, GETDATE() ) 
    AND LastLogin >= DATEADD(hh, -25, GETDATE() ) )

Answer 1

GetDate() is the timezone of the Server.

GetUTCDate() is the GMT Timezone

In your queries, always better to convert the date/datetime fields into your current timezone and then do the comparison or DATEADD functions.

If you are in UTC-4 timezone, convert your date using this

DATEADD(hh, -4, GETUTCDATE())

For GMT+8 timezone,

DATEADD(hh, +8, GETUTCDATE())

Example: If you are looking for past 7 days data in GMT+8 timezone (better to CAST the field as DATE).

CAST(ORDER_DT AS DATE) = CAST(DATEADD(dd, -7, DATEADD(hh, +8, GETUTCDATE())) AS DATE)

Your scenario WHERE condition will be something like this

CAST(LastLogin AS DateTime) <= CAST(DATEADD(hh, -24, DATEADD(hh, -4, GETUTCDATE())) AS DateTime) AND
CAST(LastLogin AS DateTime) >= CAST(DATEADD(hh, -25, DATEADD(hh, -4, GETUTCDATE())) AS DateTime)

Answer 2

In the majority of places, Marketing Cloud uses Central Standard time with no Daylight Savings.

Within the last few years, SFMC started supporting the AT TIME ZONE directive in Query Activities. With that you could do something like this in your query:

SELECT 
  u.UserId
, u.EmailAddress
, p.LastLogin
FROM ENT.[User] u
INNER JOIN Player p ON u.UserId = p.UserId
WHERE p.LastLogin <= DATEADD(hh, -24, GETDATE() AT TIME ZONE 'Eastern Standard Time') 
AND p.LastLogin >= DATEADD(hh, -25, GETDATE() AT TIME ZONE 'Eastern Standard Time')