Working with dates, need your input

hi team,

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

I use a datetime-field for colums like created or modified and set it with a CDbExp​ression('NOW()')

I would suggest the same way for date fields

there is a mysql-function date_add(CURDATE(), INTERVAL 4 DAY) I would use with CDbExp​ression()

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().

Thanks for all your responses.

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

AND

'expired' which is current time and adding 4 days

In PHP, you use time() to get the current unix timestamp. You can use time()+3600244 to get a timestamp that is 4 days after.

In MySQL, using FROM_UNIXTIME(createTime), you can work with all sorts of date-related mysql functions.

Thanks heaps Qiang,

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