Current_Timestamp Not Using Server Time

[Ninjakreborn]

I have a mysql table that uses CURRENT_TIMESTAMP on an add field. I was assuming (since it was in Mysql) that it would default to server timezone (EST) which also happens to be my timezone. So during testing, this was the case.

 

The client however is in California and he is 3 hours behind me. His complaint was, that when he tested the system anywhere on his timezone from 9-12 it should technically show up for the next day due to timezone.

 

Essentially he is saying that if he performs this action between 9-11:59 on his timezone (west coast) then during those 3 hours each night, the system should

report the action as being the next day, since it should be running on EST timezone. I do not understand why Mysql is using the local timezone instead of the timezone of the user. All I have is a datetime field in the database with the current value set to CURRENT_TIMESTAMP. Any advice is appreciated, Thanks!

[requinix]

* You can't set a DEFAULT CURRENT_TIMESTAMP on a DATETIME column. Only TIMESTAMPs. So what type is the column?

* MySQL will convert TIMESTAMPs to the current timezone when pulling the data.

* How is the user viewing the data? Surely not with a MySQL client. Which means that if the value is changing then there's something in code somewhere affecting that.

[Ninjakreborn]

1. Your right, sorry I was in a hurry when typing. The type of column is timestamp.

2. It depends. There are some actions that are done via cron by the system. In this specific situation the query is ran on the front end. When the user clicks a certain action, a series of things are checked, at which point the entry is added to the database. I use standard Codeigniter entry code, and the add_date field is automatically populated by MYSQL on creation.

 

Any advice?

[Ninjakreborn]

I did some deeper timezone tests, it is defaulting to the server timezone. Turns out just the front end display was off. Thanks again for feedback on that.