IBM Support

MySQL adds an hour to a timestamp while subtracting an interval from a timestamp literal.

Troubleshooting


Problem

An expression is defined which will subtract an interval of days from a timestamp literal. When an application connects to MySQL using MySQL Connector/J 8.0.X,  the hour of the timestamp will be incremented by an hour. 

Symptom

If the user executes the following SQL against MySQL server using  MySQL Connector/J 8.0.X, then the user will get '2018-04-01 01:00:00.0' in the results instead of '2018-04-01 00:00:00.0', which is the expected outcome.

SELECT ( TIMESTAMP '2018-04-01 00:00:00.0'   - interval(0) day) AS `Test`) FROM TABLE_NAME

Note: This issue only occurs with timestamp literals and does not occur when a column of timestamp data type is used as shown below:

SELECT (TABLE_NAME.COL_TIMESATMP   - interval(0) day) AS `Test`) FROM TABLE_NAME

Document Location

Worldwide

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSTSF6","label":"IBM Cognos Analytics"},"Component":"","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB76","label":"Data Platform"}},{"Product":{"code":"SS6G84","label":"IBM Cognos Analytics on Cloud"},"Business Unit":{"code":"BU048","label":"IBM Software"},"Component":" ","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB76","label":"Data Platform"}}]

Log InLog in to view more of this document

This document has the abstract of a technical article that is available to authorized users once you have logged on. Please use Log in button above to access the full document. After log in, if you do not have the right authorization for this document, there will be instructions on what to do next.

Document Information

Modified date:
06 May 2025

UID

ibm16116908

Page Feedback