How to store datetime in UTC timezone

Asked by Mikhail Olkhovskiy on 2013-09-17

I am trying to replace MySQL Connector/J on MariaDB client library in our project.
We are using serverTimezone option to specify server timezone. We store datetime values in UTC and Connector/J converts all datetimes from UTC and to UTC.
MariaDB client library doesn't have this option.
How can I specify timezone for MariaDB client library?
Now it use current jvm timezone.

Question information

Language:
English Edit question
Status:
Answered
For:
MariaDB Edit question
Assignee:
No assignee Edit question
Last query:
2013-09-17
Last reply:
2013-10-03
Alexander Barkov (abarkov) said : #1

Which data types do you use to store datetime values in the database?

TIMESTAMP or DATETIME, or something else?

Thank you for response.
We use DATETIME.

Alexander Barkov (abarkov) said : #3

There is no a function in MySQL/MariaDB client library to convert time zones.

The easiest way is to alter you tables to use TIMESTAMP data type instead.
It supports time zones and respects the @@TIME_ZONE session variable.

Your client program can send a query "SET @@TIME_ZONE='+04:00'"
if the client is in 4 hours east from UTC.

or use "SET @@TIME_ZONE='+00:00', if you need to access to the timestamps in UTC format.

See the documentation on the TIMESTAMP data type and @@TIME_ZONE system variable for further details.

Yes TIMESTAMP may be useful, thank you.
But, maybe it would be better to implement serverTimezone option into MariaDB client library?

Alexander Barkov (abarkov) said : #5

I'm not sure I understand the idea.

Suppose you send a query through the client library:

INSERT INTO t1 (datetime_column) VALUES ('2001-01-01');

in which moment of time would this serverTimezone option act,
and what exactly would it do?

Alexander Barkov (abarkov) said : #6

Also, can you please clarify what are you exactly moving to?

> I am trying to replace MySQL Connector/J on MariaDB client library in our project.

- to "MariaDB client library" (which is libmysqlclient - C API)
- or, to Maria Connector/J (JDBC driver)

I suggest to add JDBC connection string parameter "serverTimezone" (http://dev.mysql.com/doc/refman/5.0/es/connector-j-reference-configuration-properties.html)
And use it in MySQLPreparedStatement class to specify timezone

    public void setDate(int parameterIndex, java.util.Date date) throws SQLException{
        if(date == null) {
            setNull(parameterIndex, Types.DATE);
            return;
        }

        Timezone timezone = <get from connection string parameter>

        if (timezone != null) {
                setParameter(parameterIndex, new DateParameter(date, Calendar.getInstance(getProtocol().getServerTimezone())));
        } else {
                setParameter(parameterIndex, new DateParameter(date));
        }
    }

calendar used in

    public static void writeDate(OutputStream out, java.util.Date date, Calendar calendar) throws IOException {
        out.write(QUOTE);
        String dateString;
        if (calendar != null) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            sdf.setCalendar(calendar);
            dateString = sdf.format(date);
        } else {
            dateString = date.toString();
        }
        out.write(dateString.getBytes());
        out.write(QUOTE);
    }

> Also, can you please clarify what are you exactly moving to?
replace MySQL Connector/J on MariaDB JDBC driver

There is a documented sessionVariables parameter, that you can use to set the "time_zone" variable, via connection URL. Is this not enough for your purposes? If not (and using Calendar in PreparedStatement setDate does not cut it), please clarify and create a task in JIRA (CONJ). Thanks

Vladislav,
We use DATETIME to store dates, so "time_zone" can't help. Thanks for suggestion.
I have created task CONJ-65.

Launchpad Janitor (janitor) said : #11

This question was expired because it remained in the 'Open' state without activity for the last 15 days.

Arjen Lentz (arjen-lentz) said : #12

As a general recommendation, my company tends to advise people to run all database and web servers in UTC. This way you'll always have log files in a sane time regardless of their datacenter, and data cannot be mangled with double timezone conversions and the like.
Timezone conversions are something that need to happen very close to the user interface, because it's user specific.
Your clients may exist in a couple of different timezones, so this way you provide the best user experience.

Arjen Lentz (arjen-lentz) said : #13

set status to answered, as there has been ample discussion and elaboration.

Can you help with this problem?

Provide an answer of your own, or ask Mikhail Olkhovskiy for more information if necessary.

To post a message you must log in.