DEV Community

Cover image for Day 27: The Semantic Boundaries Between Date, DateTime and Time
Jeff Tsai
Jeff Tsai

Posted on Originally published at hackmd.io

Day 27: The Semantic Boundaries Between Date, DateTime and Time

The order date on a document, the event time on an audit record and the start time on a shift schedule answer different questions. Once they are in a DataSet, though, the first two are both DateTime columns and the third is a string column, and the column type alone cannot tell them apart. So what a column is has to be stated separately, by the definition layer.

The screen relies on it to pick an editing control, the database relies on it to pick a native type, and when a value travels between client and server, it decides whether a time zone conversion happens (that is the subject of Time Zone Conversion of DateTime). None of these can be decided from the fact that it is a DateTime alone.

This post covers:

  1. How to tell the three kinds of time apart
  2. Calendar dates and points in time are both DateTime in a DataSet, so what keeps their meaning
  3. Why a time of day does not use the database's native time type
  4. Empty values and values out of range

1. Whether the value needs to know which day it is

08:00 on a shift schedule and 08:00 on a clock-in record look the same but mean different things: the first applies every day, while the second stands only for the one time it happened.

The framework splits time fields into three kinds by meaning, and the test is a single question: does this value need to know which day it is?

Kind Question it answers FieldDbType Type in code Time zone conversion Examples
Calendar date Which day Date DateOnly Not converted A birthday, an invoice date, an accounting period
Point in time Which day and what time DateTime DateTime Converted A creation time, a login timestamp, a clock-in record
Time of day What time within a day Time TimeOnly Not converted The start and end of a shift, business hours, a reminder time

They are declared like any other field, one line per field:

<FormField FieldName="hire_date"  Caption="Hire Date"  DbType="Date" />
<FormField FieldName="created_at" Caption="Created At" DbType="DateTime" />
<FormField FieldName="work_start" Caption="Work Start" DbType="Time" />
Enter fullscreen mode Exit fullscreen mode

DbType also decides the default control on screen, which is where FormLayout and Screen Generation for Multiple Front Ends mapped a field's type to a control: calendar dates and points in time get a date input control, and a time of day gets a fixed width time input control. If a field names its own control, that one is used.


2. The two kinds that are both DateTime in a DataSet

Once in a DataSet, calendar dates and points in time are both DateTime columns, and a DateOnly value cannot be written into one. The framework handles them like this:

Calendar date Date Point in time DateTime
Column type in the DataSet DateTime DateTime
Marker on the column Date DateTime
When read Read as a DateOnly with CDateOnly Read as a DateTime with CDateTime

With the same type, the two can only be told apart by the marker on the column. Code that has to decide reads the marker first and looks at the CLR type only when there is no marker, and a DateTime is always taken to be a point in time.

The marker governs how a value is read, not how it is written. Code that writes row["hire_date"] = DateTime.Now directly leaves the hours, minutes and seconds in the DataSet along with the date; if you want only the date, take .Date yourself.

Where the marker comes from in each situation, and whether it is kept:

Situation Marker
A column created from the definition Attached when the column is created
A table read back from the database The framework adds it from the FormSchema
Your own SQL There is no definition to look up, so whoever writes the SQL lists the calendar date columns on the query, or marks the table afterwards; a misspelled column name throws an exception
Sent over the API as JSON or MessagePack The other end restores it

A calendar date column without a marker is treated as a point in time, and no error is raised, because its value is a perfectly valid point in time. Where that goes wrong is for the next post.


3. Why a time of day does not use the database's native type

How a time of day is held in each place:

Where What it looks like
In the database A fixed width string of five characters, such as "08:30"
In the DataSet The same string, "08:30"
When code reads it Converted to a TimeOnly with CTimeOnly
When it is written back to the DataSet The time of day control on a single record form pads "8:30" to "08:30"; other ways of writing it may not, and code that writes it has to convert with CTimeString itself

A TimeOnly cannot go into a DataSet directly. The time of day column is a string, so setting a TimeOnly on it does not throw; the value is stored as text in the current culture (under zh-TW that is 上午8:30, 8:30 AM written in Chinese). Change the column type to TimeOnly instead, and JSON and MessagePack do not recognize it when they send the table. So a time of day column is formatted as an "HH:mm" string, and converted to a TimeOnly when it is read.

Code uses TimeOnly because what it represents is exactly a time within a day: two of them can be compared directly, the hour and the minute can be taken out, and subtracting one from another gives a duration. The end of a shift minus its start is the hours worked, and that holds even for a night shift that crosses midnight. A TimeSpan represents a length of time, which can exceed a day and can be negative; store a start time in one, and the type cannot stop a value longer than a day.

The database does not use a native time type because the five databases disagree. TIME in SQL Server and PostgreSQL is a time within a day, TIME in MySQL is a length of time that ranges up to plus or minus 838 hours, and Oracle has no TIME at all; on top of that, the drivers for the first three all read it back as a TimeSpan. So all five use a string:

Date DateTime Time
SQL Server date datetime2(7) nchar(5)
PostgreSQL date timestamp char(5)
MySQL DATE DATETIME(6) CHAR(5)
SQLite DATE DATETIME VARCHAR(5)
Oracle DATE TIMESTAMP(6) VARCHAR2(5)

The advantage of a string is that a plain SELECT is readable as it is, and when every row is zero padded, lexicographic order is time order, so WHERE work_start BETWEEN '08:00' AND '17:00' simply works. An unpadded "8:30" sorts after "17:00", and that condition does not pick it up. The cost is that the database reports this column only as a string of length 5, so when the framework compares structures it first has to treat the time of day in the definition as a string of length 5 as well; otherwise every upgrade would find a difference.


4. Empty values and values out of range

The reading methods for the three kinds are written the same way: the method name is the return type, and the single parameter overload always returns a nullable (all three are in ValueUtilities.Temporal):

DateOnly? day     = ValueUtilities.CDateOnly(row["hire_date"]);
DateTime? instant = ValueUtilities.CDateTime(row["created_at"]);
TimeOnly? start   = ValueUtilities.CTimeOnly(row["work_start"]);
Enter fullscreen mode Exit fullscreen mode

They return a nullable because a time of day has no value that can stand for not filled in. default(TimeOnly) is 00:00, and 00:00 can itself be a start time; use it to mean empty, and a field nobody filled in is taken as 00:00, again with no error. A date could use 0001-01-01 to mean empty, but that value is easily printed on a report and taken as data, so the two date methods return a nullable as well. When you need a non null value, use the two parameter overload and supply the substitute yourself. Storage works the same way: a time of day that was not filled in is stored as an empty string, not "00:00".

Reading also checks format and range: CTimeOnly("8:30") is accepted and taken as 08:30, while "25:00" and a TimeSpan of 30 hours both come back null. A string column and a TimeSpan can both hold a value longer than a day, so it is the reading method that keeps the range in check.


Back to Northwind

The case study's FormSchema definitions have only two time fields of the application's own, and both are the same kind: order_date on orders and hire_date on employees, each declared with DbType="Date" in its definition.

The case study's TableSchema files do contain a batch of point in time columns, and not one of them is on the application's own tables: the write and expiry times of sessions, the expiry time of API keys, the update times of definitions and of cache notifications, the event time on each of the audit tables, and the write times on several other framework tables. All of it is data the framework records for itself.

The reason is that the two kinds of data serve different purposes. An order date is used for billing, for dividing into periods and for month-end reconciliation, and what time of day the order was placed is of no use to the business. When a session expires, or in which second a login failed, means nothing without the hours, minutes and seconds. By the test in the first section, the application's tables want calendar dates and the framework's tables want points in time.


In closing

Calendar dates and points in time are both DateTime in a DataSet and are told apart by the marker on the column; a column without one is treated as a point in time, and no error is raised. Tables the framework creates or reads back are marked, while with SQL you write yourself, you declare the calendar date columns yourself.

A time of day is a fixed width string in both the database and the DataSet, so it cannot be mistaken for a point in time, and code converts it to a TimeOnly when reading it. The cost is that the database knows it only as a string of length 5, which the framework has to handle separately when comparing structures.

The next post takes up the only one of the three that ever gets changed: time zone conversion of points in time. Calendar dates and times of day are not converted, and converting them only produces errors, such as a birthday slipping back to the day before or an eight o'clock start turning into four in the afternoon. All of it depends on first being able to tell which kind a column is.


πŸ“š 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-27-temporal-semantics

πŸ“’ You are welcome to republish this, please credit the source.
πŸ“¬ Follow for more engineering notes:
Facebook | HackMD | GitHub | NuGet

Top comments (0)