Should I use a timestamp or text field in MySQl for UTC time/date

Question

I read a mysql timestamp can only hold a value from 19700101000000 to sometime in the year 2037. I seriously doubt my app will be around then, well i'm sure it wont but any idea what will people use then for a timestamp, a text field?

Below is an example of how I currently insert a mysql record with a datetime mysql field and you can see I use now() in the PHP below, now I am re-coding my site and I am going to offer timezone support for showing correct times to users so I needd to save date and time as a UTC timestamp.

<?PHP
$sql = "INSERT INTO online_users 
    (user_id,
    online_id,
    ip,
    datetime,
    location,
    gender) 
    values ('$cache->userid,$unid,$LOCALIP,NOW(),$location,$g)";
executeQuery($sql);
?>

Based on my code above and my information above, how would I format this code to insert the time and date into UTC and should I use a timestamp field or a text field in MySQL? I will need to modify the time in my php when showing it often, for example on some posts I calculate the time that has passed since the mysql time (2 days and 4 hours and 34 seconds ago). I also need to use time to query for things like users in the past week to sign on. I am not asking how to do that stuff I just posted it to show what I will be using time for just in case that would help determine the best method I should be saving it as.

Answer 1

I would use the timestamp field for dates, since it will make sorting/filtering faster than it would be on a text field. In 26 years, you can check back with us to figure out what you need to do to get past Y2K37.

;-)

Answer 2

The best column for storing datetimes is a DATETIME. It simplifies your code and queries, as you don't have to convert back and forth to integer timestamps all the time, and MySQL handles all the date comparison logic for you.

You can use the MySQL UTC_DATE(), UTC_TIME() and UTC_TIMESTAMP() functions in place of NOW() to get the current UTC timestamps inside MySQL.

Answer 3

If you need to support different timezones, the wisest choise for dates would be the TIMESTAMP column type, even though it only has a range up to 2038-01-19 03:14:07. That would still give you 28 years to think about how to fix that problem.

The benefit of using a TIMESTAMP column type is that all dates are saved as UTC and converted to the required timezone on request. This way you don't have to worry so much about timezones. It doesn't solve all your problems, though.

Answer 4

You can also use ON UPDATE CURRENT_TIMESTAMP to update the field whenerevr the row is updated

Answer 5

I tried everything mention in this question/answer but nothing worked correctly, in the end the only way I was able to get php timezones date_default_timezone_set() function to work correctly with my date and times from mysql was to store them as an integer and then it works perfect. There is no problem of slower sorting for me because I sort by ID number instead of date but to sort by a date I don't think would be too difficult once I figure out how many seconds are in between.

When I use an integer field in mysql, I can store a UTC timestamp as this 1262658989. When you use a timestamp or datetime field in mysql, it automaticly convert it to this style 2003-04-14 00:00:00 which then makes it much hard to work with timezones for some reason.

Using an interger instead I can get the UTC timestamp in PHP using this code:

gmdate('U', time())

Answer 6

I have always stored epoch/unix time as a signed integer in database fields. MySQL has UNIX_TIMESTAMP() & FROM_UNIXTIME() functions. I have and will continue to receive flak for this, but the date maths are easy, the application language support for this date format is nearly ubiquitous and I can maintain operational systems (e.g. airline scheduling info) regardless of political whims about DST corrections without fear of system/runtime updates.

A few websites don't even bother to do date conversion and allow the client's JavaScript to project it in their local time.

The time deltas that you mention (i.e. how long between last view) can be facilitated with ease as you immediately have the number of elapsed seconds.