Strange behavior of timezone

[dennys]

I found that the timezone behavior for DATETIME is a little strange, could you help to confirm it? My timezone is +8, this is my test environment.

• MySQL: 8.0.30
• Node: 20.6.1
• Mysql2: 3.6.2

Command to get connectio pool:

pool = require('mysql2/promise').createPool({
    host: xxx,
    user: xxx,
    password: xxx,
    database: xxx,
    timezone: "******"
});

This is my test data.

mysql> select rec_dt from TB1;
+---------------------+
| rec_dt              |
+---------------------+
| 2023-10-13 09:15:57 |
+---------------------+

This is my test result.

• timezone is not set, it returns 2023-10-13T01:15:57.000Z
• timezone is "local", it returns 2023-10-13T01:15:57.000Z => it should be 09:15:57, and not add 'Z'
• timezone is "Z", it returns 2023-10-13T09:15:57.000Z => it should be 01:15:57
• timezone is "xxxxx", it returns 2023-10-13T09:15:57.000Z

of course, I got an error message when I set it to xxx, Ignoring invalid timezone passed to Connection: xxxxx. This is currently a warning, but in future versions of MySQL2, an error will be thrown if you pass an invalid configuration option to a Connection

[sidorares]

the parsing code ( if you are using .query ) is here - https://github.com/sidorares/node-mysql2/blob/fa47d0b0c46b3f5e79b7adae283bb78575298429/lib/packets/packet.js#L626-L633

Can you show what's actually stored in your database ( for example, with debug: true flag ). mysql cli also does timezome conversion so it's output might not be what is in the diatabase

[dennys]

I use execute to get data like this

conn = await pool.getConnection();
const [response] = await conn.execute(t_sql, [parm]);      
for (let row of response) {
	let t_result = {
		"rec_dt": row.REC_DT,
		...
	};

And I use MySQL CLI, DBeaver, MySQL Workbench, all of them get 09:15:57

And this is the debug info, does it help?

1 85231 ==> Query#unknown name(3,,70)
        Column definition:
          name: REC_DT
          type: 12
         flags: 128
 raw: 0364656608763573736f5f64620c54425f574f524b53504143450c54425f574f524b5350414345095345525645525f4944095345525645525f49440ce00000010000fd000000000046000005

[sidorares]

I was after the actual data, not the column definition for that column ( though that also helps ) type 12 is MYSQL_DATETIME, and .execute result should go via this path:

https://github.com/sidorares/node-mysql2/blob/fa47d0b0c46b3f5e79b7adae283bb78575298429/lib/parsers/binary_parser.js#L42

https://github.com/sidorares/node-mysql2/blob/fa47d0b0c46b3f5e79b7adae283bb78575298429/lib/packets/packet.js#L256-L300

could you log all the values used to construct the date here - https://github.com/sidorares/node-mysql2/blob/fa47d0b0c46b3f5e79b7adae283bb78575298429/lib/packets/packet.js#L293C23-L293C47