How is DateTime Data Stored?

In Salesforce all Date/Time data is stored in UTC — Coordinated Universal Time. UTC is not a time zone but rather a time standard used commonly around the world.

For practical purposes, we can assume that UTC is the same as the older GMT (Greenwich Mean Time) standard and is the current time in London, UK.

CopyStorm also stores all Date/Time values in UTC and it is up to an application to convert UTC values to the timezone of each application using the data. This makes a lot of sense for systems with users accessing the same data from wildly different timezones. Each user expects to see Date/Time values displayed in their local time zone!

This section lists a few techniques for converting from UTC to the local timezone of the computer hosting the CopyStorm database.

Example query:

SELECT systemmodstamp
,DATEADD(hh,((DATEDIFF(ss,systemmodstamp,GETDATE())-DATEDIFF(ss,systemmodstamp,GETUTCDATE()))/3600), systemmodstamp) AS LocalTimestamp
FROM Account

There are two things to note with this solution:

• SQL/Server 2016 has a direct solution to this problem (see this article).
• This example does not take daylight savings time into consideration.
  • See this script for a solution that takes daylight savings time into consideration.

Example query:

SELECT systemmodstamp, systemmodstamp + (systimestamp – SYS_EXTRACT_UTC(systimestamp)) LocalTime FROM Account

Example query:

SELECT systemmodstamp, systemmodstamp + INTERVAL (TIMESTAMPDIFF(SECOND, UTC_TIMESTAMP() , NOW())) SECOND FROM Account

Example query:

SELECT systemmodstamp, systemmodstamp AT TIME ZONE ‘UTC’ FROM Account