2

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

nbk
  • 45,398
  • 8
  • 30
  • 47
Peter Dang
  • 45
  • 5
  • which timezone has your server? and your connection ? – nbk Jul 03 '23 at 12:25
  • 1
    please add new information to the query and not as comment – nbk Jul 03 '23 at 12:43
  • also try https://stackoverflow.com/questions/2934258/how-do-i-get-the-current-time-zone-of-mysql to get the necessary exact information – nbk Jul 03 '23 at 12:45

1 Answers1

1
const connection = mysql.createConnection({
  host: '****',
  user: '****',
  password: '****',
  database: '****',
  timezone: 'utc'
})

adding the timezone when initializing the connection fixed the issues for me

Peter Dang
  • 45
  • 5
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jul 04 '23 at 13:40