[mythtv-users] OT mysql datetime

[Hika van den Hoven]

Hoi Nicolas,

Wednesday, April 29, 2015, 12:38:54 AM, you wrote:

> Am 28.04.2015, 18:15 Uhr, schrieb <mythtv-users-request at mythtv.org>:

>> Message: 4
>> Date: Tue, 28 Apr 2015 14:48:48 +0200
>> From: Hika van den Hoven <hikavdh at gmail.com>
>> To: Discussion about MythTV <mythtv-users at mythtv.org>
>> Subject: [mythtv-users] OT mysql datetime
>> Message-ID: <56203854.20150428144848 at gmail.com>
>> Content-Type: text/plain; charset=us-ascii
>> Hoi All,
>> I'm working on some new unrelated mysql database and I'm encountering
>>  some weird datetime behaviour. I was wondering if any of you could
>>  enlighten me. The server is running mysql 5.6.22
>> I've created a new table with both a timestamp and a datetime field.
>>  The timestamp field gets filled with local time values. If I use NOW()
>>  or CURRENT_TIMESTAMP in an INSERT query I get UTC time?
>> I don't mind in this database which is used, but of cause I want it to
>>  be consistent. What am I doing wrong? Both server and the workstation
>>  is correctly setup on timezone and running on UTC? I don't think I
>>  explicitly set up mysql timezone. It gets it from the system.
>>
> Hi Hika,

> I just checked this on my system and there now() inserts the current  
> localized time (UTC+2).

> The ref manual is quite interesting as well:  
> http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_now

> Maybe things will work as expected if you set your systems timestamps to
> the correct localized times?
> Btw. you are speaking of the mysql function now() as well? Cause I mixed
> this up with the php function some times ago which e. g. may have its own
> localization settings.

> Good luck,
> Nicolas

Thanks, as I wrote in my second post, I found something in the manual.
I still have to read further into the details.
From http://dev.mysql.com/doc/refman/5.6/en/datetime.html

" The TIMESTAMP and (as of MySQL 5.6.5) DATETIME data types offer
automatic initialization and updating to the current date and time.
For more information, see Section 11.3.5, “Automatic Initialization
and Updating for TIMESTAMP and DATETIME”.

MySQL converts TIMESTAMP values from the current time zone to UTC for
storage, and back from UTC to the current time zone for retrieval.
(This does not occur for other types such as DATETIME.) By default,
the current time zone for each connection is the server's time. The
time zone can be set on a per-connection basis. As long as the time
zone setting remains constant, you get back the same value you store.
If you store a TIMESTAMP value, and then change the time zone and
retrieve the value, the retrieved value is different from the value
you stored. This occurs because the same time zone was not used for
conversion in both directions. The current time zone is available as
the value of the time_zone system variable. For more information, see
Section 10.6, “MySQL Server Time Zone Support”.            



Tot mails,
  Hika                            mailto:hikavdh at gmail.com

"Zonder hoop kun je niet leven
Zonder leven is er geen hoop
Het eeuwige dilemma
Zeker als je hoop moet vernietigen om te kunnen overleven!"

De lerende Mens