timezones and timestamps?

[chiprivers]

I have started and restarted working on a current project several times, with varying approaches to handling the date and time.  Because the project is very date/time orientated, I need to get this just right for it to work properly and efficiently.

 

After struggling to get several bits to work as expected, I found the the timezone in my php.ini file was set to Berlin when I am in the UK.  This has solved a couple of issues.

 

However, before I get abck to the drawing board and decide on a final approach for this project, I am just interested in how other people handle dates and times in their scripts.

 

Firstly, dates and times stored in a mysql database, should I use the DATETIME format in the database and convert to timestamp if required, or store as a timestamp in an INT column?  Does either approach give a quicker response when searching or sorting by date/time?

 

Now I have my php.ini amended to default to Europe/London timezone, do I need to use gmdate and gmmaketime etc to ensure daylight saving time is taken into account, or will this be done automatically?

 

Any other advice regarding date and time handling before I start to restructure my design would be much appreciated.

 

Thanks in advance. 

[fenway]

Use the TIMESTAMP columns, and mysql will be able to auto-convert for you.

[roopurt18]

For a time sensitive application I would consider storing everything in GMT time and convert it to user's timezone upon display.

 

(edit) Additionally if you're going to be importing datetime data from outside sources consider very carefully if you want to save those columns with timezone information.  For example I have a PostgreSQL database that a mirrors a FoxPro database.  The FoxPro database does not store timezone information but I made all of my PostgreSQL columns timestamp (0) with time zone.  Eventually this turned out to be a serious bug and it was a nightmare to correctly fix all of the imported data.