Handling Events, Timestamp Or Datetime.

I read a lot of posts in the forum but I don’t know wich is the best way.

I have a table Events (like facebook) and I want to show in one view the list of events in a range of dates or display the next 10 events.

Timestamp or datetime?

Sorry for my english, and thanks in advance.

Do you mean in the database? I generally use a timestamp field because I believe it handles time zones better than datetime (it converts to and from UTC for storage and retrieval). There are caveats though; you will need to make sure the fields are set up correctly in the database so they don’t get assigned automatically on creation or update.

See the top two answers here for more info to help your decision.

Yes, in mysql. In my case I use 2 diferents timezones. (utc and utc+1)

Thanks for the link, it seems that datetime is best for compare, intervals, etc. And timestamp for record puntual marks like create time updated time.

Another question, if I want to store a birthday date, can I use timestamp if the date was before 1970?

No, but you wouldn’t need to store the time, so you could just use a date field. I wouldn’t recommend either timestamp or datetime if only the date is required.

I don’t understand this part:

There are caveats though; you will need to make sure the fields are set up correctly in the database so they don’t get assigned automatically on creation or update.

Timestamp fields act strangely in MySQL. Read through this page for more details.

Ah! there is a timestamp format in Mysql? I was thinking that I store the timestamp format in a varchar field in mysql.

Definitely don’t use a varchar for a numeric field; use an unsigned integer.

Ok, thanks.

So, can I do a query in Yii easily that shows the next events for December and populate a calendar with separated days?

I need to improve my english…

Firstly, if you want to be able to query on dates and times easily, you should probably use one of MySQL’s native types, such as timestamp or datetime. Once you’ve done that, it should be fairly easy to construct a query to get whichever range of records that you want.

You’ll want to use the CDbExpression class to make use of MySQL’s native date processing functions when you write your Yii models.