Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can we always fetch date column as string (varchar) with knex and postgres?

I have a column in postgres database with type date. It is a column like birthday, which is just a date and does not need to have a time part.

When fetching this column with knex, the result is a javascript Date object. It is presumably doing new Date(row.birthday), and this is the result that is sent to the client.

The problem now is that the value that the client receives is in the standard ISO 8601 format with the time part and the Z. When the client tries to create a new Date object from this string, the client may have an erroneous date value based on where the client is located.

For example:

Date: 2018-06-15
Date sent to client: 2018-06-15T00:00:00Z

Client in +5:00     | Client in -5:00
2018-06-16 05:00:00 | 2018-05-15 10:00:00

This is fine if the server is in UTC, we could just add the client's timezone offset and get the original date, but that feels a little fragile and it breaks during local development (which is not in UTC).

A simple solution is to just send the date part as string to the clients. But that would require storing the date as varchar in the server, which we don't want to do. We would lose the date formatting constraint and will make it harder to do date based calculations with SQL.

We could cast the column as varchar when selecting the column but we need to have the presence of mind to do it every time this table is fetched. That would also mean we need to go around the ORM (Bookshelf) to work with this table.

Is there an easy way to specify, either in knex or bookshelf or postgres itself, that a column needs to be stored as date, with all the accompanying constraints, but always be fetched as varchar?

like image 478
squgeim Avatar asked Jun 06 '18 08:06

squgeim


People also ask

What is the datatype for date in PostgreSQL?

The date format for the date data type in PostgreSQL is yyyy-mm-dd . This is the format used for both storing data and for inserting data.

What does KNEX query return?

Knex returns an array for all queries, even if there is only one row returned. The name of the user can be accessed from user[0] .

How can set primary key in KNEX?

As per Knex's documentation here: primary — column. primary([constraintName]); table. primary(columns, [constraintName]) When called on a single column it will set that column as the primary key for a table.

What is KNEX NPM?

Knex is a SQL query builder, mainly used for Node. js applications with built in model schema creation, table migrations, connection pooling and seeding.


1 Answers

node-postgres driver is the part which actually creates Date() objects from data sent from date columns (https://node-postgres.com/features/types#date-timestamp-timestamptz)

With postgres you can modify node-pg's type parsers like described in here https://github.com/brianc/node-pg-types

Date types type's oid which is 1082 can be fetched with following query

select typname, oid, typarray from pg_type where typname = 'date' order by oid;

So to to override date type to be passed as string it is enough to do this before setting up your db connection (I suppose one could do that for example in knexfile.js):

var types = require('pg').types;
// override parsing date column to Date()
types.setTypeParser(1082, val => val); 
like image 173
Mikael Lepistö Avatar answered Sep 21 '22 19:09

Mikael Lepistö