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
?