PEAR Date and storing times in database

[FiRe]

I am using the PEAR Date module which allows me to set timezones and build a calendar. If a user stores a date in my mysql database, should I store this as a datetime field or as a unix timestamp? Bare in mind the Date class will use this date and this must be relevant to their timezone...

[edited by: FiRe at 4:25 pm (utc) on May 22, 2008]

[coopster]

Do you intend to port this code to other platforms in the future, like Windows? If so, you may find issues with dates when using certain functions, both in MySQL and PHP. The timestamp issue is getting better cross-platform but my personal preference is still DATETIME (TIMESTAMP).

As far as relevant to their timezone, you can either store that in your table, or set a cookie whenever the user visits. Take WebmasterWorld for example. We have people from around the globe here and I want to know when your message was posted. If it displayed your time zone and timestamp, what good does that do me? Now I have to figure out the relative period of time compared to my time zone. If I was you, I would store the value in a common format (UTC) and adjust accordingly based on the visitor's preferences (visitor's specified timezone).