Properly Storing TimeStamps in DB

drag0n

I am creating a user account table in mysql and I have a few fields that are concerened with date/time; such as Last login, Registeration Date, etc.
What would be the best way to store this type of data in MySQL?
What type should these fields be? varchar()?
Also; the time zone on my server is different than what the users on my website are in. What would be the best way to make sure the time is correct for all my users? Should I make them select their location?

Thanks

NogDog

Store them as either DATETIME or TIMESTAMP types in the DB, depending on whether or not you need the added ability of TIMESTAMP types to automatically set themselves on insert or update queries. If the value in your PHP script is a PHP timestamp integer, you can convert it to the necessary date string via the [man]date[/man] function:

$dateString = date('Y-m-d H:i:s', $dateTimeVariable);
$sql = "INSERT INTO table_name (`date_time_col`) VALUES('$dateString')";

drag0n

And what about the time-zones problem?

NogDog

drag0n wrote:
And what about the time-zones problem?

3 options I can think of:

Have the users select their timezone/offset, then convert date/time values accordingly.
"Guess" their timezone by using an IP-to-location database that includes the necessary timezone info.
Convert all date/time values to GMT (UTC) time and display them as such (e.g.: 2008/07/15-02:39 UTC) and let them figure it out.

For 1 and 2, take a look at the various timezone-related functions in the Date/Time Functions section of the PHP Manual for setting the default timezone in your scripts and for the lists of recognized timezones.