How does sql databases and drivers handle Date columns

How does 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