英文:
mySQL DATE functions in ExpressJS evaluates to a day earlier than input
问题
我使用express连接到mySQL，每当我尝试使用任何日期函数时，它总是评估为提供的日期早一天，时间总是下午5点。
例如：
connection.query('SELECT DATE("2001-02-15")', (err, rows, fields) => console.log(rows))
这会产生以下输出：
[ RowDataPacket { 'DATE("2001-02-15")': 2001-02-14T17:00:00.000Z } ]
另一个示例是当我在7月3日运行这个查询时：
connection.query('SELECT CURDATE()', (err, rows, fields) => console.log(rows))
输出为：
[ RowDataPacket { 'CURDATE()': 2023-07-02T17:00:00.000Z } ]
但是使用mySQL shell给出了正确的日期：
+------------+
| CURDATE() |
+------------+
| 2023-07-03 |
+------------+
1 row in set (0.0008 sec)
我的mySQL服务器使用我的系统时区，时区设置为UTC +7，并且mySQL Workbench中的system_time_zone变量显示为"SE Asia Standard Time"。有人知道发生了什么吗？
编辑：值得注意的是，使用NOW()返回的时间比我的时间早7小时，这表明ExpressJS出于某种原因处于UTC+0（当前时间是7:32）：
connection.query('SELECT NOW()', (err, rows, fields) => console.log(rows))
输出为：
[ RowDataPacket { 'NOW()': 2023-07-03T12:31:13.000Z } ]
查询时区信息：
SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | SYSTEM |
+--------------------+---------------------+
我认为它们都使用系统时区，根据我的笔记本电脑的时区设置，系统时区为UTC+7。
英文:
I'm connecting to mySQL using express, and anytime I try to use any DATE functions it always evaluates to one day earlier than the provided date and always at 5pm
For example:
connection.query('SELECT DATE("2001-02-15")', (err, rows, fields) => console.log(rows))
This gives the output:
[ RowDataPacket { 'DATE("2001-02-15")': 2001-02-14T17:00:00.000Z } ]
Another example when I ran this on July 3:
connection.query('SELECT CURDATE()', (err, rows, fields) => console.log(rows))
[ RowDataPacket { 'CURDATE()': 2023-07-02T17:00:00.000Z } ]
Using the mySQL shell gives me the correct date though:
+------------+
| CURDATE() |
+------------+
| 2023-07-03 |
+------------+
1 row in set (0.0008 sec)
My mySQL server is using my system's time zone, which is set to UTC +7, and the system_time_zone variable in mySQL workbench says SE Asia Standard Time. Anyone have any idea what's going on?
Edit: worth noting that using NOW() gives a time 7hours earlier than my time, which suggests ExpressJS is for some reason in UTC+0 (my time currently is 7:32)
connection.query('SELECT NOW()', (err, rows, fields) => console.log(rows))
[ RowDataPacket { 'NOW()': 2023-07-03T12:31:13.000Z } ]
query for time zone
SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | SYSTEM |
+--------------------+---------------------+
i think they both use system time zone, which is UTC+7 according to my laptop's time zone settings
答案1
得分: 1
const connection = mysql.createConnection({
host: '',
user: '',
password: '',
database: '',
timezone: 'utc'
})
添加时区在初始化连接时为我解决了问题。
英文:
const connection = mysql.createConnection({
host: '****',
user: '****',
password: '****',
database: '****',
timezone: 'utc'
})
adding the timezone when initializing the connection fixed the issues for me
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论