[ale] MySQL DATETIME

[Alex Carver]

This depends on how the "started" field is declared.  If it's a
TIMESTAMP then MySQL autoconverts any input to UTC as it stores to disk
and then feeds it back out according to the local TZ.  A DATETIME field
does not perform an autoconversion.

You can change the time zone on a per connection basis.  One solution
would be to declare the field as DATETIME (to stop auto conversions) and
then either explicitly specify time zones in any queries (e.g. make sure
your date and times are entered to be YYYY-MM-DD HH:MM:SS Z) which will
ensure correct storage on disk or always declare the time zone when
establishing the connection so that the reads and writes will come out
the same.  You can issue "SET time_zone = timezone;" upon connection to
establish the time zone for that connection.

Alternatively you can change the default timezone on your slave in its
own configuration.

On 2014-05-30 17:18, Chris Fowler wrote:
> I think I just found out something nasty.
> 
> ubuntu at arm:~$ date;salt 'select
> inbound_call_log_id,started,UNIX_TIMESTAMP(started) from
> inbound_call_log limit 1'
> Sat May 31 00:16:56 UTC 2014
> +---------------------+---------------------+-------------------------+
> | inbound_call_log_id | started             | UNIX_TIMESTAMP(started) |
> +---------------------+---------------------+-------------------------+
> |                   1 | 2014-05-22 16:53:39 | 1400777619 |
> +---------------------+---------------------+-------------------------+
> 
> [support at support ~]$ date ; mysql  -h 127.0.0.1 -e 'select
> inbound_call_log_id, started,UNIX_TIMESTAMP(started) from
> inbound_call_log limit 1' SALT;
> Fri May 30 20:16:40 EDT 2014
> +---------------------+---------------------+-------------------------+
> | inbound_call_log_id | started             | UNIX_TIMESTAMP(started) |
> +---------------------+---------------------+-------------------------+
> |                   1 | 2014-05-22 16:53:39 | 1400792019 |
> +---------------------+---------------------+-------------------------+
> [support at support ~]$
> 
> support is a mysql slave.  Does this mean my data is only valid if I
> restore it on a server in the UTC zone?  The slave is in US/Eastern and
> the master is in UTC.
> 
> Chris