MySQL, PHP date functions

Question

Since I started programming in PHP.MySQL I have had a problem with date functions, this mostly due to a learning issue I have with numbers. I have never had any formal training and have had to rely on reading material that hasn't made a whole lot of sense to me. I have managed to get by, but probably do more work than is necessary.

Having said this, I am working on an invoice/appliance repair script which will have 3 dates in it, when the call came in, when it is scheduled to be fixed, and the completion date.

These dates may have to be searched on by a user using a web form. The user would enter something like 10/05/2011. There may also be MySQL queries on weeks, months, etc. Example, I need a query to find all the records from previous years.

So what would be the best way to store these in MySQL and what should the field type be, and what would be the best way to extract these into a human readable form.

I know this is a loaded question, but just reading documents about all these different date functions has just confused me more, so any help is appreciated.

Thanks, CR

Bailey Parker · Accepted Answer · 2011-01-24 05:10:25Z

3

You could store it as a simple string and use the LIKE operator to do searches. But I suggest that you store as DATE or DATETIME. This way you'll be able to use MySQL date comparisons and functions on them.

answered Jan 24, 2011 at 5:10

Bailey Parker

15.9k5 gold badges58 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Roger Halliburton · Accepted Answer · 2011-01-24 05:17:33Z

0

Your field type choices are basically date and datetime. I don't think there is any difference in the actual space used to store a date without the time, and if you don't capture the time you might regret it later so you might want to default to datetime.

You can compare a datetime field the same way you check integer fields: equals, greater than, less than. All of the date functions are for special things you might want to do, I am particularly fond of DATE_ADD() and TIMESTAMPDIFF(). The formatting functions are good for generating printable date formats without having to use some iffy php library or trying to write your own.

answered Jan 24, 2011 at 5:17

Roger Halliburton

2,0213 gold badges15 silver badges19 bronze badges

Collectives™ on Stack Overflow

MySQL, PHP date functions

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related