How do sql databases and drivers handle Date columns
I usually get confused when handling date datatype with databases, especially when connecting to the databases via libraries/drivers.
In this post, I will summarize the results of their specification after my tests.
Environment:
- Node:
v16.0.0. - Postgres nodejs driver:
pg@8.6.0. - Mysqlite nodejs driver:
sqlite3@5.0.2. - Knex query builder:
knex@0.95.6. - Postgres server:
13.0.
With SQLite
Type definition
SQLite data types reference can be referred from here.
The type used in the test: datetime.
Test result
| Set type | Get type | Sample set | Sample get |
|---|---|---|---|
number |
number |
1622361110420 |
1622361110420 |
| ISO 8601 string | ISO 8601 string | "2021-05-30T07:51:50.420Z" |
"2021-05-30T07:51:50.420Z" |
Native Date object |
string |
new Date(1622361110420) |
"Sun May 30 2021 16:51:50 GMT+0900 (Japan Standard Time)" |
With PostgreSQL
Type definition
Postgres date type definition and specification can be found here.
Column type used in the test: timestampz(6) (timestamp with 6 digits precision with time zone information included).
Test result
| Set type | Get type | Sample set | Sample get |
|---|---|---|---|
number (error) |
n/a | 1622361110420. error: date/time field value out of range: "1622361110420" |
n/a |
| ISO 8601 string | Native Date object |
"2021-05-30T07:51:50.420Z" |
Native Date object |
Native Date Object |
Native Date object |
new Date(1622361110420) |
Native Date object |
For parsing data from database
to customize the bound types, use pg-types package. There is a list of type ids defined here. TIMESTAMP (1114) and TIMESTAMPZ (1184) are associated with the native parser here.
For serializing data to pass to query
This logic is, in fact, irrelevant to the data parser. pg package has a special check on the native Date object.
if (val instanceof Date) {
if (defaults.parseInputDatesAsUTC) {
return dateToStringUTC(val)
} else {
return dateToString(val)
}
}
The pg package has its own version of Date object serialization namely dateToStringUTC and dateToString, and use one of them depending on whether the parseInputDatesAsUTC option is used. Note that this option is not officially documented.
