3

In multi-tier systems, where the only place where you you have a concept of "localtime" is many tiers away from the database. Therefore, I'm not a fan of the mysql column type of timestamp, because it means that data returned is dependent on the configuration of the server and the connection of the client. I would much rather use datetime columns and be explicit about timezones.

Since mysql 5.6.5, there is support for defaults, but it appears that the only default for datetime columns is CURRENT_TIMESTAMP, which sets a datetime column to the local time.

Is there anyway, other than triggers, to have a datetime creation column, which stores the creation time in UTC?

bpeikes
  • 3,495
  • 9
  • 42
  • 80
  • 1
    This is one of funnier posts I've read today. `timestamp` does what you want, it was created for that very purpose - but you don't want to use it because of random statements that are not even true. Why do you complicate your life? You use `timestamp`, and there is no server configuration that can affect it, except being out of sync with the "actual" time - which we deal with using `ntpdate pool.ntp.org` if needed. However, you can always save the time in a `datetime` column and time zone information in another column, if you prefer working that way. – N.B. Apr 07 '15 at 15:23
  • I'm not sure what random statements that you are talking about. timestamp columns, although stored in UTC, are returned from queries using the timezone of the server, not UTC. Additionally, unless I'm mistaken, using DEFAULT CURRENT_TIMESTAMP with a datetime column will store the time in localtime of the server, not UTC. – bpeikes Apr 07 '15 at 17:21
  • `timstamp` **stores** the time in UTC. That is what your question is about. It only formats it based on the values provided by the server's time zone primarily, and based on connection session value secondary. Your options are to use the timestamp and to set the connection session variable to UTC - this is the least painful way to perform what you asked in entirety. Now, alternative is to completely forget about formatted date/time storage and to use triggers, integer column and `UNIX_TIMESTAMP()` function to fetch the seconds since 1970. Choose your poison and good luck. – N.B. Apr 07 '15 at 20:14
  • 1
    I was hoping to avoid triggers since you can now have defaults for DATETIME via CURRENT_TIMESTAMP. Ideally you would be able to have a default for a DATETIME, which was CURRENT_TIMESTAMP_UTC. It seems odd that CURRENT_TIMESTAMP saves the time as UTC when used with a TIMESTAMP column, but saves the time in local time when used with a DATETIME column. – bpeikes Apr 08 '15 at 04:04
  • 1
    I agree with you, it would be extremely useful to have a way to save UTC date and retrieve UTC date and to be able to use defaults in table definitions for setting the values when left empty. But sadly, we don't. Though, we might ask people over at MariaDB to see what they think about this potential feature. In the mean time, we're stuck with triggers and our own efforts. – N.B. Apr 08 '15 at 07:56

3 Answers3

3

It appears that there is no way to set the default value for a DATETIME to be "NOW" in UTC.

bpeikes
  • 3,495
  • 9
  • 42
  • 80
0

Unfortunately it looks like there's no way to do this. I even tried doing this:

create table test_table (
    id int,
    test_date timestamp default convert_tz(now(), 'America/New_York', 'UTC')
);

Unsuccessful. Strangely, the only function you can pass when creating a table is now(), it seems.

gav.newalkar
  • 302
  • 3
  • 11
-1

The value is always come from your system's time whether you are using timestamp or datetime. Steps to your question-

  1. you can set timezone to utc by- How do I set the time zone of MySQL?

  2. create table date (id int , date datetime default now());

  3. insert into date(id) values (1);

now you can see the result .

  1. select * from date;

i think this can help you

Community
  • 1
  • 1
Hitesh Mundra
  • 1,538
  • 1
  • 9
  • 13
  • I understand that the time comes from the server. The issue is that I would like to capture the UTC time, which timestamp columns do, but I don't want the auto conversion to localtime when querying the data out since the application which displays the data may be in a completely different timezone as the database. As far as I can tell, there is absolutely no reason for a mysql server to know that timezone it's in, other than to capture the time in UTC. – bpeikes Apr 07 '15 at 17:24
  • this answer is doing what you mention in your question. date column store a timestamp value in utc. now i am not getting what you want ? can you set an example ? – Hitesh Mundra Apr 08 '15 at 02:16