How to store datetime in UTC timezone

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.

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?

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?

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.

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

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.

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