Re: Connector 8 - Datetime Convertion

[Filipe Silva]

Hi Stella,

There are many variables to consider here:

1. What was the client and server time zones at the time of the insertion?
2. How did you inserted the date value? Have you used a prepared statement or a plain statement with the date value represented as String inside the query?
3. What was the client and server time zones at the time of the retrieval?
4. What is the date value you are expecting to get? And what if you consult the same from another client running on a different time zone?
5. Are you using date setter/getter methods that take a Calendar instance?

The answer to these questions determine how you should setup your connections to MySQL and how to use the JDBC API.

My first advice is: ALWAYS use temporal data setters/getters from PreparedStatement/ResultSet that take a Calendar instance as argument. Those are the ones that should behave more consistently across all configurations. This is how you tell the driver what exactly are you storing or retrieving.

If this is something you cannot do, then it all depends on what do you expect:
- Do you want that different clients, running from different time zones, set/get date values "adjusted" to their time zones? Then, at the moment, Connector/J operates like this by default so you don't need to do anything.
- OTOH, do you want to get the "same" time values whatever is the client time zone? Then you should set the connection option `serverTimezone=<client_timezone>`, where <client_timezone> is the canonical name of the timezone where the client is running, e.g., Europe/London.

My last advice is: DON'T EVER insert date values using plain statements and queries with values concatenated locally. This is fine for testing or if you know what's happening underneath, but it's generally a bad idea for production systems.

IHTH