0

I'm having a bizarre issue with my local MySQL server. I have a table with a created_at timestamp column set to CURRENT_TIMESTAMP by default. Everything I've read indicates that this should store the time in UTC, then convert it back to local time for display. However, it's not working right. I'm not sure if it's screwing up on the display or what, but when I read the timestamps out, it's giving me the GMT times instead of adjusting for my local timezone. This wouldn't be as big of an issue, except that NOW() is giving me local time, so I can't compare them properly. I feel like I'm probably missing something stupid, but what's going on here?

If it matters, I'm running MySQL 5.5.24 under 64-bit Windows 8 (unfortunately).

Edit:

Poking at it some more has revealed that the timestamps are working fine when I add rows manually; it's just the rows coming from my PHP that are screwy. I guess the PHP connection must be overriding the default timezone for some reason.

user1618143
  • 1,728
  • 1
  • 13
  • 27
  • you could use `Now()`...like `before insert` SET newcol = NOW()`... – bonCodigo Jan 09 '13 at 18:53
  • Check this [post as well](http://stackoverflow.com/questions/381371/sqlite-current-timestamp-is-in-gmt-not-the-timezone-of-the-machine?rq=1) :) – bonCodigo Jan 09 '13 at 19:00
  • Well, I could adjust the time zones manually, but then what happens when I move this out to the real server? Is that still appropriate, or will that box work the way I was expecting this one to? I kind of need to understand what's happening here. – user1618143 Jan 09 '13 at 19:24
  • You may need to move a dummy to the `real` server and see.. `NOW()` will always work. oh it could be some mismatch – bonCodigo Jan 09 '13 at 19:49

2 Answers2

0

Have you loaded time zone data?

http://www.geeksengine.com/article/populate-time-zone-data-for-mysql.html

wless1
  • 3,489
  • 1
  • 16
  • 12
0

Ah-hah! My PHP server and my MySQL server were using different timezones. Needed to go add date.timezone = America/New_York to my php.ini file.

user1618143
  • 1,728
  • 1
  • 13
  • 27