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

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

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.