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

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');

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.