[mythtv] Re: [mythtv-commits] Re: Ticket #723: MythTV should use UTC, not localtime for Dates Stored in the database

[Kevin Atkinson]

Bruce Markey wrote:

> POSIX/Unix/Linux/etc. as well as MySQL store time in seconds
> since midnight Jan 1, 1970. Libraries translate these times
> for human display based on the configured timezone. Neither
> MythTV nor MySQL choose a time zone but use the system env.

That is only true for the "timestamp" type NOT "datetime".  Using mysql 
4.0.24:  Using the current timezone:

   mysql> create table test (d datetime, t timestamp);
   mysql> insert into test values (NOW(), NOW());

   mysql> select * from test;
   +---------------------+----------------+
   | d                   | t              |
   +---------------------+----------------+
   | 2005-12-02 17:41:32 | 20051202174132 |
   +---------------------+----------------+

Now I change the timezone to UTC by setting TZ=UTC and restarting mysql (with 
version 4.0.24 that is the only way to do it)


   mysql> insert into test values (NOW(), NOW());

   mysql> select * from test;
   +---------------------+----------------+
   | d                   | t              |
   +---------------------+----------------+
   | 2005-12-02 17:41:32 | 20051203004132 |
   | 2005-12-03 00:43:20 | 20051203004320 |
   +---------------------+----------------+

Notice how the first row of the row with the datatime type doesn't change but 
the timestamp one does.