How do sql databases and drivers handle Date columns

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.