Rectangle 27 1

Some debugging done in Issue 1642 indicates that this is due to how JDBC works with MySQL, and the connection flags required to fix it currently can't be set in Apps Script.

To be clear through, the timezone shown when converting a JavaScript Date to a string will always be the timezone of the server, in this case Pacific Time. Use Utilities.formatDate to display a Date in a specific timezone.

google apps script - Date returned with Jdbc is in PDT timezone - Stac...

jdbc google-apps-script
Rectangle 27 0

MySQL has extensive timezone support. You can set it on server level, on a per connection basis and even per query. I think executing 'SET time_zone = xxx' in __construct is enough for you.

MYSQL set timezone in PHP code - Stack Overflow

php mysql timezone
Rectangle 27 0

If the data source doesn't have such a property, you can extend it and add that property:

public TimezoneEnabledDataSource extends BasicDataSource {
    private String timezone;
    //getter and setter for it

    @Override    
    public Connection getConnection() {
        Connection c = super.getConnection();
        // execute a query: SET time_zone = '-8:00'
        return c;
    }
}

Per-connection time zones. Each client that connects has its own time zone setting, given by the session time_zone variable. Initially, the session variable takes its value from the global time_zone variable, but the client can change its own time zone with this statement:

You can also check if c3p0 doesn't have something built-in.

@Bozho, this looks like a solution!!! I'll focus some time on this! Did you see any problem by doing this kind of solution!?

it's from the top of my head. I don't actually see a problem, but I haven't used it in production ;)

I don't think it is a good idea to define some properties in your database in a multithreaded enviroment :S

@Treydone I didn't understand your comment. I think the query sets the timezone per connection, not globally (at least that's what the article claims)

@Treydone what are your concerns about it? I'm asserting that when works with timestamp columns, MySQL will not change my date due to different timezones. Thinking that MySQL will always be started at UTC.

java - Setting connection timezone with Spring and DBCP and MySQL - St...

java mysql spring jdbc timezone
Rectangle 27 0

Since you're using Windows, your going to have to import the time zone description tables to your MySQL server.

This is not limited to Windows, on Ubuntu I also had to load the timezone table as described in here stackoverflow.com/questions/5510052/

mysql_tzinfo_to_sql /usr/share/zoneinfo | sed 's/Local time zone must be set--see zic manual page/(ERROR)/' | mysql -uroot mysql

Sign up for our newsletter and get our top new questions delivered to your inbox (see an example).

Changing the connection timezone in MySQL - Stack Overflow

mysql timezone
Rectangle 27 0

TIMESTAMP storing without timezone, timezone applying by mysql server when you retriving data from Mysql, so you need properly set timezone for you mysql connection like this,

SET time_zone = 'Europe/Helsinki';
SET time_zone = "+00:00";
SET @@session.time_zone = "+00:00";

Better practice is set +00:00 for connection, and after aplying timezone on client in begin php script for example

php - Modify TIMESTAMP to reflect correct TIMEZONE - Stack Overflow

php mysql timezone timestamp
Rectangle 27 0

Yes, that is indeed a good way. Also keep in mind that if you use the TIMESTAMP type in MySQL, MySQL handles the timezone converting for you. When you insert new dates/times to the database, MySQL converts it from the connection's timezone to UTC (TIMESTAMP is always stored in UTC). When you retrieve a TIMESTAMP field from database, MySQL converts it back to the connection's timezone.

So if you use TIMESTAMP fields in MySQL, all you need to do is tell the user's timezone to MySQL at start of each your page. You do so by:

SET time_zone = 'Europe/Helsinki'

You can also use numeric timezones:

SET time_zone = '+02:00'

Keep in mind that you might need to install the tzinfo to MySQL first, which is trivial though (only for the non-numeric version though). Here's information about how to do it: http://dev.mysql.com/doc/refman/5.0/en/mysql-tzinfo-to-sql.html

In a nutshell, this is the important part:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Here's an example of how it works:

mysql> CREATE TEMPORARY TABLE test(foo TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)

mysql> SET time_zone = '+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test VALUES ('2011-02-03 16:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> SET time_zone = '+02:00';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT foo FROM test;
+---------------------+
| foo                 |
+---------------------+
| 2011-02-03 18:00:00 |
+---------------------+
1 row in set (0.00 sec)

If you don't use TIMESTAMP field, eg. you use DATETIME (which also supports wider range of dates), then you just need to make sure you always insert dates in UTC; I do this by always setting connection's timezone to +00:00. Then you can have a view helper in PHP that converts the datetime to the user's timezone, which is quite trivial to do with PHP's DateTime class and setTimezone function. There's an example in the last link. To use this method, you must make sure PHP is also set to use UTC as its default timezone, which you can do with this:

date_default_timezone_set('UTC');

Whichever method you use, you should always be aware of these facts:

  • The PHP and MySQL connection's timezones should always be set to the same value so they're consistent with each other
  • Generally it's a bad idea to mix TIMESTAMP and DATETIME types with each other
  • If you use TIMESTAMP type, set the timezone to the user's timezone
  • If you use DATETIME type, set the timezone to UTC and handle timezone convertions in PHP

So if the column EVENT_STARTDATE is set to timestamp, and I use mysql_query to set the timezone to the user's timezone, When I select the EVENT_STARTDATE it will be converted automatically to the user timezone?

Attached a test-case in the answer about TIMESTAMP behavior.

GREAT answer, seriously. Thanks a lot.

php - Support user time zones - Stack Overflow

php mysql
Rectangle 27 0

You probably need to set the proper timezone in your MySQL server or your connection to that server.

php - MySQL datetime is 2 hours off? - Stack Overflow

php mysql datetime
Rectangle 27 0

There is no "sessionTimeZone" member in the BasicDataSource. Use C3P0 which is a "better" connection pool than DBCP, or even better, if you are in a Java EE web server, use it to initialize a JNDI datasource ;)

Hi Treydone, thanks for your help. I saw somewhere that is possible to configure that using C3P0, but I don't know if is a option, keeping in mind that is a huge system very solid. But I'll look for your solution!

Interestingly enough there is no sessionTimeZone or equivalent member in C3P0 DataSources either...

java - Setting connection timezone with Spring and DBCP and MySQL - St...

java mysql spring jdbc timezone
Rectangle 27 0

For Linux, BSD, and Mac OS X if you need to load the timezone table do this:

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -uroot mysql

Changing the connection timezone in MySQL - Stack Overflow

mysql timezone
Rectangle 27 0

you can set MySQL Timestamp column type and:

<bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource" destroy-method="close">
    <property name="driverClass" value="com.mysql.jdbc.Driver"/>
    <property name="jdbcUrl" value="${jdbc.url}"/>
    <property name="username" value="${jdbc.user}"/>
    <property name="password" value="${jdbc.pass}"/>
    <property name="idleConnectionTestPeriodInMinutes" value="60"/>
    <property name="idleMaxAgeInMinutes" value="240"/>
    <property name="maxConnectionsPerPartition" value="30"/>
    <property name="minConnectionsPerPartition" value="10"/>
    <property name="partitionCount" value="3"/>
    <property name="acquireIncrement" value="5"/>
    <property name="statementsCacheSize" value="100"/>
    <property name="initSQL" value="SET time_zone='${database.timezone}'"/>

this is my bonecp dataSource -> see the initSQL this query is executed on the init of every mysql connection. If you web server is in Europe/Brussels set this time zone to ${database.timezone} this works for me. If you mysql have empty timezone tables you neeads to import it form Linux locales like:

java - MySQL JDBC date issues with database server in different timezo...

java mysql jdbc timezone
Rectangle 27 0

you can write a c3p0 ConnectionCustomizer which sets the time zone back to UTC onCheckin. See

jdbc - Setting connection timezone dynamically with Spring and c3p0 an...

mysql jdbc c3p0 spring-jdbc
Rectangle 27 0

date_default_timezone_get()
SET time_zone = ""

Is this php/mysql expression (which sets the timezone of connection) s...

php mysql pdo timezone
Rectangle 27 0

TIMESTAMP is, essentially, a DATETIME that is automatically converted to UTC when stored by MySQL and automatically adjusted for the timezone of specified to the MySQL connection. TIMESTAMP also has a smaller date storage range than DATETIME.

PHP MYSQL date, time, datetime and timestamp - Stack Overflow

php mysql
Rectangle 27 0

Have you tried using a zoneinfo name such as "America/New_York" instead of the abbreviation (which is only valid for half the time)?

Ideally, you shouldn't use the server time zone at all of course - store dates in UTC and then convert it to the appropriate time zone in the client code.

how to store dates in Utc

i try that SET time_zone = 'America/New_York' but it doen't work,i generates error #1298 - Unknown or incorrect time zone: 'America/New_York'

@gowri: Without knowing anything about how you're inserting data into the database (e.g. which language/platform) it's hard to know what to suggest in terms of storing in UTC. As for valid time zones - I believe that's operating-system dependent; which operating system are you using?

i'm using php and oc is windows xp,server wamp

@gowri: You may find it works if you can find out the Windows time zone ID for New York. I don't know it offhand. As for UTC... are you trying to insert the current time? If so, use UTCTIMESTAMP(). Alternatively, just setting the time zone to "UTC" might do what you need...

Changing the connection timezone in MySQL - Stack Overflow

mysql timezone
Rectangle 27 0

You should be able to put the same SQL statements in the initConnectionSqls property of the DBCP configuration element. Just add this to the DBCP configuration element

<property name="initConnectionSqls" value="SET time_zone = '${database.timezone}'"/>

Depending on your version of DBCP, you may have to use connectionInitSqls as the property name. This information is straight from DBCP configuration documentation.

java - Setting connection timezone with Spring and DBCP and MySQL - St...

java mysql spring jdbc timezone
Rectangle 27 0

MySQL stores its native date/time types as UTC on the server and converts them to other timezones on a per-connection basis. If a connection does not specify a timezone it tries to use the servers time. You can read more about this in the documentation. But you should be able to do:

SET time_zone = timezone;

to the client timezone at the start of the query or if you do per request connections.

php - Adjusting for timezones and DST - Stack Overflow

php mysql datetime
Rectangle 27 0

useTimezone is an older workaround. MySQL team rewrote the setTimestamp/getTimestamp code fairly recently, but it will only be enabled if you set the connection parameter useLegacyDatetimeCode=false and you're using the latest version of mysql JDBC connector. So for example:

String url =
 "jdbc:mysql://localhost/tz?useLegacyDatetimeCode=false

If you download the mysql-connector source code and look at setTimestamp, it's very easy to see what's happening:

If use legacy date time code = false, newSetTimestampInternal(...) is called. Then, if the Calendar passed to newSetTimestampInternal is NULL, your date object is formatted in the database's time zone:

this.tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss", Locale.US);
this.tsdf.setTimeZone(this.connection.getServerTimezoneTZ());
timestampString = this.tsdf.format(x);

It's very important that Calendar is null - so make sure you're using:

setTimestamp(int,Timestamp).

It should be obvious now how this works. If you construct a date: January 5, 2011 3:00 AM in America/Los_Angeles (or whatever time zone you want) using java.util.Calendar and call setTimestamp(1, myDate), then it will take your date, use SimpleDateFormat to format it in the database time zone. So if your DB is in America/New_York, it will construct the String '2011-01-05 6:00:00' to be inserted (since NY is ahead of LA by 3 hours).

To retrieve the date, use getTimestamp(int) (without the Calendar). Once again it will use the database time zone to build a date.

Note: The webserver time zone is completely irrelevant now! If you don't set useLegacyDatetimecode to false, the webserver time zone is used for formatting - adding lots of confusion.

It's possible MySQL my complain that the server time zone is ambiguous. For example, if your database is set to use EST, there might be several possible EST time zones in Java, so you can clarify this for mysql-connector by telling it exactly what the database time zone is:

String url =
 "jdbc:mysql://localhost/tz?useLegacyDatetimeCode=false&serverTimezone=America/New_York";

You only need to do this if it complains.

Thank you for the useLegacyDatetimeCode suggestion! Note: This also solved my problems working with Joda Time (via the usertypes hibernate add-on) as well.

So with serverTimezone and useLegacyDatetimeCode added to my connection string, I can send a Timestmp and it gets recorded correctly in a DateTime column as it's UTC time. I can see it on my MySQL console as such. When I do a SELECT via JDBC and try to get the data out, it still automatically converts it in the JDBC Layer. I've tried getObject() and geTimestmp (both without and with a UTC calendar object) and it keeps coming back in local time.

How to change mysql timezone in java connection - Stack Overflow

java mysql timezone
Rectangle 27 -1

Don't think about converting or adapting time zone. Don't think about the TZ the mysql uses to store your timestamps or anythink like that. Those things are already handled. There are three things that you must handle: INPUT, OUTPUT and bugs.

When a user enters a date (in a form) without an explicit time zone you have to know what TZ did he intend to use. You can use a SimpleDateFormat object with time zone set to solve this. You don't have to convert the input date, you have to 'interpret' it correctly. Once you have a correctly interpreted Date or timestamp you are done with input.

Input is not only user input, includes configuration files too.

The same here. Forget about what TZ have your Date objects and timestamps have none, they are just milliseconds since epoch. You have to format your dates to the TZ the user expects so he understand them.

You may have bugs in your code related to TZ, but libraries may have them too!!

I noticed mysql java driver failed to communicate the client timezone to the server. This command s.executeUpdate ("set time_zone='+xx:yy'"); is the workaround but you are using it wrong. You have to tell the server with it the TZ the client is using, before both inserting and querying. The variable is stored in the session. Maybe you may automatize it on your connection pool config. This is needed so the server know what TZ the client need to use to read or write. This is not dependent on server TZ. It does not mean "store this date in UTC", it does mean "this date I am giving to you is UTC" and "Send me result sets in UTC". No matter you are using Date class with it's internal TZ, the driver screws it up, you would need to set that session variable.

By default it assumes client TZ is the same as server TZ so you shouldn't need to worry about it as you said they are the same.

This is bad advice. SQL dates in general (and mysql in specific) are stored as year/month/.../sec fields, not millis since the epoch. Time zones are relevant for the conversion to Java dates, and only "handled" for you if you want the default behavior, which OP does not. For non-trivial applications, you do need to think about time zones, at both the server storage level and conversion steps, or you may encounter subtle bugs.

@AndrewJanke If the server can give you the dates in any time zone why do you need to store them in a certain time zone? The only risk to avoid is thinking one date has a certain TZ being wrong. I don't see the point on working with 'string dates' with a specific time zones but to read/write from/to the user. I develop SCADA systems used and configured by users on different time zones and simply work with jave.util.Date objects or timestamps without any TZ problem.

You need to be concerned with what time zone you store them in on the server to avoid ambiguities in time zone conversion. For time zones which use daylight saving time, certain wall times like "2:00 AM" can occur twice on "fall back" days, and there's not enough information in the SQL date to disambiguate them. And if you don't have known, consistent, non-ambiguous time zones, server-side date arithmetic may be wrong.

@AndrewJanke Well. That's broken in mysql. stackoverflow.com/questions/1646171/ I guess you need to use UTC on server, as we do.

It's not just broken on MySQL, it's a common issue with many SQL RDBMSes, and any other data store that can store dates in naive "local" times without additional DST indicators. Which means yes, you have to think about how the server is storing the dates. Running your server in UTC is a good fix for this (I run mine in UTC too for this reason). But as OP stated, is not an option for them.

Retrieving UTC DATETIME field from MySQL in Java when server timezone ...

java mysql date datetime jdbc