[ale] MySQL DATETIME

[Alex Carver]

On 2014-06-02 13:16, Chris Fowler wrote:
> On 05/30/2014 09:23 PM, Alex Carver wrote:
>> 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.
> 
> This would be the best way.  I can then run queries via the mysql client
> using the TZ environment variable.
> 
> Do you know if it is possible, and how, I can do an alter table to
> convert the datetime column to a type of timestamp?

You'll probably have to do it in stages to make sure there are no
unexpected conversions.  Set the TZ to the appropriate location, read
out the DATETIME field to a temporary table in parts (i.e. year, month,
day, hour minute, second,  not as a epoch number), convert the field in
the original table to TIMESTAMP (to enable autoconversion), then push
back all the values allowing an autoconversion to take place as it is
stored on disk.  Just make sure the data makes sense as you read it out
to the temporary table.