Wrong query result with JOIN on TIMESTAMP column

Help
1

I’m using MemSQL v6.7.16.
I executed query which joins 2 tables on date_trunced timestamp column like “SELECT col1, col2 FROM table1 LEFT JOIN table2 ON date_trunc(‘minute’, table1.col1) = date_trunc(‘minute’, table2.col1) WHERE date_trunc(‘minute’ table1.col1) between ‘2020-12-10 12:00:00’ and ‘2020-12-10 12:20:00’;”, but it returned data at wrong time like 2020-12-10 21:15:00 or 2020-12-10 03:13:00. The time gap is always 9 hours and I execute query at Asia/Tokyo timezone, so I guess this is a bug around timestamp type.
Is there any known issues about this problem?

2

Have you tried using the CONVERT_TZ function? I use it to convert timestamps in our database to MST. Try this: CONVERT_TZ(col1, ‘GMT’,‘+9:00’). Hope this helps. Best, Kevin

Link to the documentation: SingleStoreDB Cloud · SingleStore Documentation

3

Time zone is not part of our datetime or timestamp types. Make sure your data is normalized to a single time zone, preferably the time zone of your cluster installation and OS.

After you check that, if you still have a problem, please include a small repro with the table and a few rows of data in insert statements, and the query, and the expected vs. actual results.

Also, since timestamp runs out of dates in 2038, everybody should use datetime for new development.

1 Like