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 |