0

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.

Thom A
  • 88,727
  • 11
  • 45
  • 75
cybertoast
  • 1,343
  • 13
  • 19
  • 1
    The timestamp isn't wrong. `GetDate()` returns the *local* time and I'd bet summer rules apply on your server now. Setting the timezone to London still means that British DST rules apply. If you want UTC times use `GETUTCDATE()` – Panagiotis Kanavos Apr 13 '22 at 12:31
  • In fact, your results show exactly this: On the first server the timezone is `(UTC+00:00) Dublin, Edinburgh, Lisbon, London`. On the second server it's `(UTC) Coordinated Universal Time`. The functions in the `SELECT` statement are misleading too. A `datetime` has no offset so `getdate() AT TIME ZONE 'UTC'` will return the time value with the offset corresponding to the `UTC` timezone, not convert the time to UTC. Use `datetimeoffset` to avoid such issues. That type includes the offset so there are no assumptions – Panagiotis Kanavos Apr 13 '22 at 12:36
  • `a misconfiguration of one RDS instance vs the other.` there are two problems here, both a misconfiguration and an assumption that everything is UTC. If `datetimeoffset` was used, it wouldn't matter what timezone the server is on. `SYSDATETIMEOFFSET()` would return the correct time *and* the offset, whatever that is. That value would be equal to the same instant in any other offset, eg `12:00 +00:00` is equal to `13:00 +01:00`. The actual offset of each server wouldn't matter. By using a type without offset though, all servers now must use UTC – Panagiotis Kanavos Apr 13 '22 at 12:40
  • @PanagiotisKanavos - thanks so much for the details. If DST is the culprit, is this something that can be configured in RDS? The 2 servers above both were created at the same time, in the same region (AFAIR). Also, when I try with `select sysdatetimeoffset() [datetimeoffset with timezone];` on the DST server, I'm still seeing the time off by an hour (ie BST). Am I misunderstanding what you're saying? Also is there a way to UN-DST (ie re-UTC) the instance? – cybertoast Apr 13 '22 at 13:36
  • The machines may have been created together but they don't have the same system settings. The [Amazon RDS docs](https://aws.amazon.com/premiumsupport/knowledge-center/rds-change-time-zone/) say that RDs instances by default are at UTC. So either someone actually changed the timezone on one machine or RDS has a bug. Unfortunately `You can’t modify the time zone after you create the instance.` so you'll have to recreate it – Panagiotis Kanavos Apr 13 '22 at 15:01
  • From everything I've read the only solution is: 1. create new RDS instance and make sure it has the correct timezone; 2. Backup DB from old RDS and restore to new RDS; 3. shut down old RDS; 4. check all applications are working as expected. This works well. – cybertoast Apr 15 '22 at 09:23

0 Answers0