I have a couple of queries regarding working with dates and time.
What is the best way to save a date and time in a database? datetime or int?
If i have the current date how do i add 4 days to this time so that I can add and 'expired' field to my database?
At this stage I am using UTC_TIMESTAMP for my 'created' and 'updated' fields as I thought this would be the best way as I am working on a travel application.
I usually use integer to represent UNIX timestamp. If you need to convert it to date, datetime in mysql, you can use DATE(FROM_UNIXTIME(createTime)) and so on.
I like qiang's approach with int(10) and mktime(), but prefer datetime() type as I work a lot with phpMyAdmin.
For a human eye, you wont determine what date is it looking at you if it is an integer, and of course you can easely say what date is it when it is a datetime.
This question appears because different servers are in different timezones and mysql NOW() function would return different date, so int(10) solve this problem.
So, what i do is working with php function date('Y-m-d H:i:s').
For mysql date manipulation I use DATE_ADD(NOW(), INTERVAL 4 DAY) or DATE_SUB('$now', INTERVAL 3 MONTH) where $now could be = date('Y-m-d H:i:s').
And to solve problem with timezones I create a second field like
createdOn DATETIME NOT NULL,
createdGmtOn DATETIME NOT NULL
the second is to save gmdate('Y-m-d H:i:s') and if you need to process it in int, use php function gmmktime().
In your personal opinion is it better to assign date and time via php or mysql. Is there a better way. Take into consideration that I am working on a travel site where flights cross timezones. cheers