Recommendations for saving datetime and timezone data in a database when data depends on date and time

Question

There were quite a few questions about storing datetime and timezones in the database, but more about the general level. Here I would like to address a specific case.

System specifications

• We have a systematic database of orders.
• This is a multi-user system in which tenants can use an arbitrary time zone (this is an arbitrary but single time zone for each tenant, which is stored in the Tenants table once and never changes).

Business rule to be included in the DB

• When a tenant places an order in the system, the order number is calculated based on their local datetime (this is not just a number, but some type identifier ORDR-13432-Year-Month-Day). Accurate calculation is not important at the moment, it is simply important that it depends on the local time of tenants.
• We also want to be able to select all Orders at the system level placed between certain UTC time dates, regardless of the tenant (for general system statistics / reporting)

Our initial idea

• Our initial idea was to save the UTC date and time across the entire database and, of course, keep the tenants' time offset relative to UTC and have an application that consumes DB that always converts datetimes to UTC so that the database itself always works with UTC.

Approach 1

• datetime , , :

  SELECT * FROM ORDERS WHERE OrderDateTime BETWEEN UTCDateTime1 AND UTCDateTime2
  

, OrderDateTime , . , Tenants datetime, OrderDateTime , . , , ?

2

• , UTC, OrderNumber, // UTC datetime

; , 6 , UTC, - 2017-01-01 02:00. UTC 2016-12-31 20:00. , , OrderNumber 'ORDR-13432-2017-1-1', UTC ORDR-13432-2016-12-31.

, , UTC, OrderNumber , DateTime UTC.

• ?
• UTC, - ?
• UTC, 2) - / ?

[]

:

, , , - . , , . .

, . , .

Answer 1

Hugo , :

• , . , UTC . , IANA , "America/Los_Angeles". wiki.

• OrderDateTime UTC. , , .

  • , Microsoft SQL Server datetimeoffset, UTC. , , , UTC, .

  • , UTC timestamp. timestamp with time zone, , , , , . UTC, timestamp ( ) datetime .

• UTC, , , . , . . , UTC .

  date (, , datetime , datetimeoffset timestamp). , , / . , .

• , , . convert_tz, IANA.

  Microsoft SQL Server, AT TIME ZONE SQL 2016 Azure SQL DB, Microsoft. IANA, ,

  , , tzdb IANA , America/Punta_Arenas, , UTC-3, (America/Santiago) UTC-4 DST.

  , , , , .

• ( , ), . , , .

Answer 2

UTC . 2.

-, , / , . , UTC.

: , UTC+06:00, 2017-01-01 02:00, 2016-12-31 20:00 UTC.

ORDR-13432-2017-1-1, date UTC 2016-12-31 20:00Z.

, :

SELECT * FROM ORDERS WHERE OrderDateTime BETWEEN UTCDateTime1 AND UTCDateTime2

OrderDateTime UTC.

, , . ( UTC+06:00), , 2017-01-01 ( ):

--get tenant timezone
--startUTC=tenant local 2017-01-01 00:00 converted to UTC (2016-12-31T18:00Z)
--endUTC=tenant local 2017-01-01 23:59:59.999 converted to UTC (2017-01-01T17:59:59.999)
SELECT * FROM ORDERS WHERE OrderDateTime between startUTC and endUTC

ORDR-13432-2017-1-1.

---

, , "" .

/ ( UTC OrderDateTime, ). , , . , .