Storing a Node.js Date in a Knex dateTime()/MySQL DATETIME field

Question

I'm having some trouble finding a date format that is common to Node.js, knex, and MySQL (via Bookshelf).

I set up a table with the Knex schema builder:

knex.schema.createTableIfNotExists("examples", function (table) {
    ...
    table.dateTime("some_datetime");
})

This creates a column with a DATETIME type in MySQL.

I have a Bookshelf model that represents this (leaving out all the boilerplate stuff here), and I attempt to use the built-in Date.now() as a value:

exampleModel.save({
    some_datetime: Date.now()
})

With debugging turned on in Knex I see the query is actually attempting to insert an epoch timestamp with milliseconds ("..." mine for brevity):

{ ...
  bindings: [ 1485644012453, ... ],
  sql: 'update `examples` set `some_datetime` = ? where `id` = ?' }

But this is incorrect, as MySQL expects you to use FROM_UNIXTIME in this case, and so the resulting date in the database is, of course, the good ol' 0000-00-00 00:00:00.

What should I be doing here to make this all consistent?

• Is there a different type I should use in the schema builder when creating the table?
• Or, is there something different I should use to obtain the date besides Date.now()?
• Or, something else?

I'm having trouble finding the common ground here. My intuition says use dateTime in Knex, Date.now() in Node, DATETIME in MySQL, but this isn't correct.

Just to be clear: This question isn't necessarily focused on what's philosophically correct -- at the moment I'm actually not able to figure out how to store date/times in the database at all. I'm looking for a working combination, semantic correctness would only be a bonus.

Answer 1

Javascript function Date.now() returns epoch in milliseconds. Mysql driver, which knexuses to send queries expects that you pass ISO8061 strings or Date() objects to DATETIME columns.

Date objects are converted to 'YYYY-mm-dd HH:ii:ss' strings

https://github.com/mysqljs/mysql

So instead of Date.now() use new Date() or new Date().toISOString()

EDIT:

Just checking that mysql really accepts .toISOString() output because I couldn't find mention about it from docs https://dev.mysql.com/doc/refman/5.7/en/date-and-time-type-overview.html

MariaDB [(none)]> select CAST('2017-01-30T16:49:19.278Z' AS DATETIME);
+----------------------------------------------+
| CAST('2017-01-30T16:49:19.278Z' AS DATETIME) |
+----------------------------------------------+
| 2017-01-30 16:49:19                          |
+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Looks like it really does. Yay! All good.

EDIT2: Actually plainly using .toISOString() causes warning and error in some case so to insert UTC DATETIME columns .toISOString().replace('Z','').replace('T', ' ') should do... Or any other preferred way to make that UTC datetime object to be converted to format yyyy-MM-dd HH:mm:ss.

Also if you are using TIMESTAMP column type and you want to insert UTC times to the database it is important to set also database session to be in UTC. Otherwise the javascript timestamps are interpret as local database times and converted from it to UTC before storing.

CREATE TABLE test (descr TEXT, dt DATETIME, ts TIMESTAMP);

SET time_zone = '+8:00';
INSERT INTO test (descr, dt, ts) VALUES 
  ('session tz +08:00 insert local times', '2020-01-01T00:00:00', '2020-01-01T00:00:00');

SET time_zone = '+0:00';
INSERT INTO test (descr, dt, ts) VALUES 
  ('session tz +00:00 insert local times', '2020-01-01 00:00:00', '2020-01-01 00:00:00');


SET time_zone = '+02:00';
select 'server local time:', now();
select * from test;

SET time_zone = '+08:00';
select 'server local time:', now();
select * from test;

---

**Query #1**

    SET time_zone = '+02:00';

There are no results to be displayed.

---
**Query #2**

    select 'server local time:', now();

| server local time: | now()               |
| ------------------ | ------------------- |
| server local time: | 2020-05-10 16:38:26 |

---
**Query #3**

    select * from test;

| descr                                | dt                  | ts                  |
| ------------------------------------ | ------------------- | ------------------- |
| session tz +08:00 insert local times | 2020-01-01 00:00:00 | 2019-12-31 18:00:00 |
| session tz +00:00 insert local times | 2020-01-01 00:00:00 | 2020-01-01 02:00:00 |

---
**Query #4**

    SET time_zone = '+08:00';

There are no results to be displayed.

---
**Query #5**

    select 'server local time:', now();

| server local time: | now()               |
| ------------------ | ------------------- |
| server local time: | 2020-05-10 22:38:26 |

---
**Query #6**

    select * from test;

| descr                                | dt                  | ts                  |
| ------------------------------------ | ------------------- | ------------------- |
| session tz +08:00 insert local times | 2020-01-01 00:00:00 | 2020-01-01 00:00:00 |
| session tz +00:00 insert local times | 2020-01-01 00:00:00 | 2020-01-01 08:00:00 |

---

View on DB Fiddle

Shows how storing and reading TIMESTAMPs are actually always handled as local times and DATATIMEs are independent from time_zone setting of the DB session.

With rapid test I was not able to make yyyy-MM-dd HH:mm:ss+zz:zz type of timestamps / datetimes to work even on mysql 8.

Answer 2

Instead of Date.now() you could use the built in now() function of Knex.js:

const knexfile = require('../../knexfile');
const knex = require('knex')(knexfile.development);
const date = knex.fn.now();

Alternative:

const knexfile = require('../../knexfile');
const knex = require('knex')(knexfile.development);
knex.raw('CURRENT_TIMESTAMP');