What datatype should I use to store time() in MySQL for my application?

Question

I plan to capture the start and end of user initiated activities on my website using time() in php. I'm not sure if this is the best way to capture start/end times. Anyway, the data will be stored in MySQL, but again I'm not sure what datatype I should use. Based on the answers I've read on stackoverflow, the datatype used depends on the purpose of the application.

Purpose of the application

1. At it's simplest, I want to record start, stop (and duration) of an activity. Probably using time().
2. At it's most complicated I'd like to plot statistics based on when the user did a certain activity, how much time they spent doing the activity (in total), and when they were the most successful/least successful etc, etc. (all based on the start/end times) Something to keep in mind. The users will be from all over the world.

MORE INFO If an activity is repeated a new record will be made for it. Records will not be updated.

At first, I had planned on storing unix timestamps in MySQL (as an integer datatype?), but from what I understand this is a bad idea, because I will lose a lot of MySQLs ability to process the information. If I store the information as DATETIME, but then move the server, all the times will change based on the local time of the server. Something I found confusing was that TIMESTAMP in MySQL is not the same as a unix timestamp- which is what I would be getting if I used time().

I'm aware that the unix timestamp can only hold dates up to 2038 for some systems, but that isn't a concern (at the moment).

Question: Should I use time() to capture start and end times for user initiated activities? Based on the purpose of the application, what datatype should I use to store the start and stop of user initiated activities?

THANKS

Thanks for the answers everyone. TBH I'm not convinced either way yet, so I'm still doing some research. I chose the TIMESTAMPS option because I really would like to store my information using UTC (GMT). It's a pity though that I will lose out on some of MySQLs inbuilt time functions. Anyway thanks again for your answers.

Solution 1

If you're going worldwide, MySQL's TIMESTAMP is almost universally a good choice over DATETIME, since it stores the time as UTC instead of local time so DST changes won't cause you problems if analyzing in multiple time zones.

Having a non DST changing time zone as a base can be a life saver, converting between multiple time zones with different DST changeover dates can really cause problems, consider for example having a timestamp during the hour that happens twice in a change from summer- to winter time.

Solution 2

Use DATETIME to store the time and use date('Y-m-d H:i:s') to get the current time to store it. When you fetch this value, you will get the time in this format.

To convert it to a timestamp, use $timestamp=strtotime($fetchedValue) To display this in another format use date('H:i',$timestamp). Read about formats from date manual of php

TIMESTAMP can only store values after Jan 1 1970, since it stores timezone data. So if you are trying to store a date before Jan 1 1970, its better to use DATETIME.

Frankly, TIMESTAMP is useful only if you are actively syncing raw data between two machines with different timezone

Comments

[TryHarder]

@zerkms Why do you recommend timestamp?

[zerkms]

because it supports all the datetime features and additionally knows about timezones

[TryHarder]

Thanks for you reply. Why do you recommend DATETIME over MySQL'S TIMESTAMP?

[SoWhat]

TIMESTAMP can only store values after Jan 1 1970, since it stores timezone data. So if you are trying to store a date before Jan 1 1970, its better to use DATETIME. TIMESTAMP , I agree stores TimeZone data, but it is only useful if you have two servers who cluster together, but use different tz data. In any case, u can use php to accomodate for it

[Joachim Isaksson]

@SomeshMukherjee TIMESTAMP does not store timezone data, it stores everything in UTC and automatically converts from UTC to your local timezone on read and from your local timezone to UTC on write. The range of TIMESTAMP is indeed smaller than the range of DATETIME though.

[SoWhat]

thats what i meant to say, my bad. I oversimplify sometimes