Yii Framework Forum: Working with dates, need your input - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • This topic is locked

Working with dates, need your input Rate Topic: -----

#1 User is offline   ooaat 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 89
  • Joined: 01-May 09

Posted 14 July 2009 - 07:31 PM

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
0

#2 User is offline   mbi 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 604
  • Joined: 08-May 09

Posted 15 July 2009 - 06:08 AM

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

#3 User is offline   qiang 

  • Yii Project Lead
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,871
  • Joined: 04-October 08
  • Location:DC, USA

Posted 15 July 2009 - 07:22 AM

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.
0

#4 User is offline   phpdevmd 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 73
  • Joined: 16-April 09

Posted 15 July 2009 - 08:59 AM

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

#5 User is offline   ooaat 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 89
  • Joined: 01-May 09

Posted 15 July 2009 - 11:17 PM

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
0

#6 User is offline   qiang 

  • Yii Project Lead
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,871
  • Joined: 04-October 08
  • Location:DC, USA

Posted 16 July 2009 - 07:11 AM

In PHP, you use time() to get the current unix timestamp. You can use time()+3600*24*4 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.
0

#7 User is offline   ooaat 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 89
  • Joined: 01-May 09

Posted 16 July 2009 - 06:24 PM

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
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • This topic is locked

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users