mysql - 如何使用 Node.js 从 Angular 将日期插入 MySQL DATETIME 列？

Question

我的应用程序中有日期选择器。我想将所选日期插入到数据类型为 DATETIME 的 MySQL 数据库列中。

这是 Angular 中使用 console.log(date.value) 的日期选择器的值:

Tue Nov 12 2019 00:00:00 GMT+0200 (Israel Standard Time)

MySQL数据库插入日期需要转换成什么格式？

Answer 1

为确保一致性，将所有日期存储在 UTC 时区中会很有帮助。

第 1 步:将 JavaScript 日期转换为 ISO(UTC 时区)

const isoDateString: string = datePickerDate.toISOString();

这还可以通过 JSON 将日期发送到服务器。

第 2 步:确保 MySQL 时区为 UTC

cursor.execute("SET time_zone = '+00:00'")

第 3 步:格式化 MySQL 插入的日期

在 Node.js 服务器上，解析 ISO 日期字符串(来自第 1 步)并将格式设置为:
'YYYY-MM-DD HH:MM:SS'

const isoDate = new Date(isoDateString);
const mySQLDateString = isoDate.toJSON().slice(0, 19).replace('T', ' ');

MySQL Documentation

MySQL recognizes DATETIME and TIMESTAMP values in these formats:

As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. A “relaxed” syntax is permitted here, too: Any punctuation character may be used as the delimiter between date parts or time parts. For example, '2012-12-31 11:30:45', '2012^12^31 11+30+45', '2012/12/31 11*30*45', and '2012@12@31 11^30^45' are equivalent.

The only delimiter recognized between a date and time part and a fractional seconds part is the decimal point.

The date and time parts can be separated by T rather than a space. For example, '2012-12-31 11:30:45' '2012-12-31T11:30:45' are equivalent.

As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string makes sense as a date. For example, '20070523091528' and '070523091528' are interpreted as '2007-05-23 09:15:28', but '071122129015' is illegal (it has a nonsensical minute part) and becomes '0000-00-00 00:00:00'.

As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.