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
?
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.
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] .
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.
Knex is a SQL query builder, mainly used for Node. js applications with built in model schema creation, table migrations, connection pooling and seeding.
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);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With