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 |