Working with dates, need your input
Posted 14 July 2009 - 07:31 PM
I have a couple of queries regarding working with dates and time.
1. What is the best way to save a date and time in a database? datetime or int?
2. 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.
Any advise would be greatly appreciated. Thanks
Posted 15 July 2009 - 06:08 AM
I would suggest the same way for date fields
there is a mysql-function date_add(CURDATE(), INTERVAL 4 DAY) I would use with CDbExpression()
Posted 15 July 2009 - 08:59 AM
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().
Posted 15 July 2009 - 11:17 PM
Qiang can you please provide an example me some actual examples as I am still a little confused.
Please provide example of a 'created' line of code
'expired' which is current time and adding 4 days
Posted 16 July 2009 - 07:11 AM
In MySQL, using FROM_UNIXTIME(createTime), you can work with all sorts of date-related mysql functions.