AWS-RDS MSSQL: timestamp for getdate() is wrong: offset by 1 hour even with timezone set as UTC

780 Views Asked by At

I have a production RDS instance which is showing an incorrect timestamp. The "UTC" time is off by an hour if I look at it with timezone. See the query output below:

proddb1> SELECT GETUTCDATE() AS getutcdate,
                GETDATE() AS getdate,
                GETDATE()AT TIME ZONE 'UTC' AS getdate_tz,
                CURRENT_TIMESTAMP AS [current_timestamp],
                SYSDATETIMEOFFSET() AS sysdatetimeoffset,
                current_timezone() AS 'current_timezone';
Time: 1.308s (a second)
-[ RECORD 1 ]-------------------------
getutcdate        | 2022-04-13 12:06:27.250
getdate           | 2022-04-13 13:06:27.243
getdate_tz        | 2022-04-13 13:06:27.2430000 +00:00
current_timestamp | 2022-04-13 13:06:27.243
sysdatetimeoffset | 2022-04-13 13:06:27.2463081 +01:00
current_timezone  | (UTC+00:00) Dublin, Edinburgh, Lisbon, London

How is the sysdatetimeoffset set, and why is it off by an hour? The RDS instance is in me-south-1a, which should be GMT+4. Presumably the database is in GMT, but then why is the timezone "Dublin" rather than "Universal Coordinated Time"?

The same query on another RDS instance in the same VPC, used for another project, shows the timestamp correctly:

SNPMOTFPROD;> SELECT GETUTCDATE() AS getutcdate,
                     GETDATE() AS getdate,
                     GETDATE()AT TIME ZONE 'UTC' AS getdate_tz,
                     CURRENT_TIMESTAMP AS [current_timestamp],
                     SYSDATETIMEOFFSET() AS '
              sysdatetimeoffset',
                     current_timezone() AS 'current_timezone';
Time: 0.466s
-[ RECORD 1 ]-------------------------
getutcdate        | 2022-04-13 12:07:34.267
getdate           | 2022-04-13 12:07:34.260
getdate_tz        | 2022-04-13 12:07:34.2600000 +00:00
current_timestamp | 2022-04-13 12:07:34.260
sysdatetimeoffset | 2022-04-13 12:07:34.2748643 +00:00
current_timezone  | (UTC) Coordinated Universal Time

Here the "current_timezone" shows the expected timezone. Also getdate() and getutcdate() show the correct value (without the 1-hour offset like above).

I would expect the getdate() and current_timestamp values to either be UTC (GMT+0) or the timezone of my RDS region (me-south-1a = GMT+4). But this is neither.

I can't find any rds db-parameters or any configurations that might indicate a misconfiguration of one RDS instance vs the other.

0

There are 0 best solutions below