The previous post, The Semantic Boundaries Between Date, DateTime and Time, ended by saying that this one would take up the only type that ever gets changed.
When a time value is written to the database, the column does not record which time zone it is based on. The same code run on a machine in Taipei and on a host whose time zone is set to UTC writes values 8 hours apart, and nothing in the table shows the difference. It is discovered only when the first user in another time zone shows up, and by then the historical data has already been written in bulk.
The framework uses a single baseline in the database, and does the conversion in a single place.
This post covers:
- The database stores UTC, in columns without a time zone
- Conversion is concentrated in the Connector on the client, and the server decides the points in time that get saved
- Which values get changed, and what the decision is based on
- An undeclared calendar date column, and clients without a Connector
1. Which time zone a stored value follows
Storing local time in the database shows no problem in a deployment with a single time zone. But "local" may mean the time zone of the application host or that of the database host. The two usually agree, and when they do not, nothing gives it away.
The framework stores every point in time as UTC in a column without a time zone, which is the type listed for DateTime in the previous post's comparison of the five databases. PostgreSQL's timestamptz converts implicitly according to the server's time zone setting, so the framework does not use it; time zone conversion is not left to the database.
When a SQL statement does not give a value for a column, what gets written is the column's default value, for example when an INSERT written by the caller leaves the column out, or when existing rows are backfilled after a column is added. So on every database the DEFAULT of a point in time column is also written in a form that returns UTC:
| Provider | DEFAULT of a point in time column |
|---|---|
| SQL Server | getutcdate() |
| PostgreSQL | (NOW() AT TIME ZONE 'UTC') |
| MySQL | (UTC_TIMESTAMP(6)) |
| Oracle | SYS_EXTRACT_UTC(SYSTIMESTAMP) |
| SQLite |
CURRENT_TIMESTAMP (already UTC) |
The outer parentheses on the MySQL line are required syntax from 8.0.13 on: apart from CURRENT_TIMESTAMP, any expression used as a default value has to be wrapped in parentheses.
2. Conversion happens in only one place
If conversion is spread around, done once on the desktop, once in the browser and once more in report export, and one of those places converts once too often or not at all, the symptom is a wrong time on screen, not an exception.
The framework concentrates conversion in the Connector on the client. The server does no conversion and reads and writes UTC directly. The Connector converts different things in each direction:
| Direction | What is converted |
|---|---|
| When a response arrives | Points in time, from UTC to the user's time zone |
| When a request goes out | Only query conditions, from the user's time zone to UTC; the DataSet being saved is left untouched |
The server does not accept the points in time sent up with a save. A new row is filled with the UTC time of the save, and a field with a default value expression is recalculated by the server; a modified or deleted row gets its original value read back from the database. So reading a record and saving it back leaves its time values unchanged. When the time zone is set wrong, the values stored in the database are not affected; what is wrong is the display on screen and the query conditions.
Another approach would be for the server to convert the points in time it receives back to UTC, using the time zone recorded on the session, and then save them. The framework did not take it. That would give the time zone two sources, the client for display and the server for writing back; when the two disagree, the user sees 09:00, leaves it alone, and a different point in time gets saved. For a form to edit a DateTime field directly, the business object has to convert it to UTC on the server itself.
The time zone used for conversion is the one on the session. It is read from the user's row at login, falls back to the deployment level default only when that is empty, and is not taken from the device: when a user travels with a laptop, the time shown for the same record, and the baseline for query conditions, should not change with the device.
3. Which values get changed
All the Connector has is a payload, with no FormSchema to look up, so the decision rests on the marker on the column from the previous post, which travels along with the data.
What is decided is whether a value is a point in time. A time of day is a string in the DataSet, so it is outside the decision.
| Where the value sits | What expresses its meaning | Converted? |
|---|---|---|
A cell of a DataSet in a response |
The marker on the column |
DateTime is converted, Date is not |
| The value of a query condition in a request | The value's own CLR type |
DateTime is converted, DateOnly is not |
| The parameter bag carried by every request | Nothing | Not converted |
A query condition has no column to hang a marker on, so its meaning comes from the value's own type (these two lines build a FilterCondition):
FilterCondition.Equal("invoice_date", someDateOnly); // calendar date, left as is
FilterCondition.Equal("created_at", someDateTime); // point in time, converted to UTC when sent
Passing a DateTime where a calendar date belongs raises no error; the condition value simply shifts along with it, and the rows found are wrong.
The values in the parameter bag are untyped, so a point in time cannot be told from a calendar date there. Converting them all would corrupt UTC values the caller put in on purpose, so they are not converted.
The message types to convert are listed one by one, and a newly added message type that carries data is not included automatically. So a separate test finds every message type that carries a DataSet, a DataTable or query conditions, runs each one, and checks that responses were converted to the user's time zone and query conditions to UTC; a type that was missed makes this test fail.
Whose today it is
When a row is added, a date field defaults to today, and this today has a time zone problem too. If it takes the server's today and the server's time zone is set to UTC, a user in Taipei who opens a document before eight in the morning gets the previous day as the default date.
The framework works out today in the user's time zone. The default value of a date field on a new row and today inside an expression use the same code, and the caller passes in the time zone on the session. This code runs on both the server and the client, and the two sides have to arrive at the same today; otherwise the same document has different dates on each side. Calendar dates are not converted, so no mechanism would make up that difference.
4. How it breaks when something is missed
When your own SQL leaves out the declaration of a calendar date column, that column is treated as a point in time and converted like any other when the response arrives. Measured with a column holding 2026-01-01 in the database:
| Who reads it | Value after conversion |
|---|---|
| A user in Taipei | 2026-01-01 08:00 |
| A user in New York | 2025-12-31 19:00 |
Taipei is east of UTC, so conversion adds 8 hours: the date stays the same and only hours and minutes appear. New York is west of UTC, so conversion subtracts, and the date falls back to the day before. The same mistake therefore looks like nothing more than extra hours and minutes in a deployment with only users in Taiwan, and becomes a wrong date only once users west of UTC show up.
A client without a Connector has to convert on its own: points in time it receives go to the user's time zone, and query conditions go to UTC. Column types arrive with the payload, and the type of a calendar date column is Date, so it can tell which columns to leave alone.
Back to Northwind
The case study is a single time zone deployment, and the demo account's row has Asia/Taipei as its time zone.
The application's own two time fields are both calendar dates, so the Connector finds no field to convert on the application's forms. The only value that conversion touches and that is also visible on screen is the creation time on the audit rule form: the list does not show this column, the single record form does, read only and showing just the date. When a rule is added from the screen, the server fills this column in at save.
In closing
Points in time in the database are all stored as UTC, and column default values are written in forms that return UTC as well. Conversion is done only in the Connector on the client: when a response arrives, it converts points in time to the time zone on the session according to the marker on the column; when sending, it converts only query conditions to UTC. The server does not accept points in time sent up with a save: a new row gets the UTC time of the save, and a modified row keeps the value from the database.
When your own SQL leaves a calendar date column undeclared, users in Taipei see only extra hours and minutes, and only users west of UTC see the day before. So calendar date columns have to be declared by whoever writes the SQL. Whether conversion covers every message type is checked by a test.
The three posts in this chapter ask the same question: what a value stands for cannot be seen from the value itself. The same decimal number can be a unit price or an amount, the same time value can be a calendar date or a point in time, and a point in time stored as UTC does not show which time zone it should be displayed in. The three posts also take the same approach: declare the meaning next to the value, and have every path that touches the value follow that declaration. The cost is the same too: when a declaration is missing, nobody reports an error, because the values left behind are still valid.
The next post, Which Part Definitions, the Framework and Application Code Each Own, goes back to the case study and puts these mechanisms in their places one by one: which of them sit in the definitions, which sit in the framework, and which the application has to write itself.
📚 Part of the thirty-post series ERP Architect's Notes: Designing a Definition-Driven Framework
📘 Original article on HackMD, in Traditional Chinese:
👉 https://hackmd.io/@jeff377/day-28-timezone-conversion
📢 You are welcome to republish this, please credit the source.
📬 Follow for more engineering notes:
Facebook | HackMD | GitHub | NuGet
Top comments (0)