Working with Dates

[johnnybravo]

Hi

 

I am currently building a Content Managment Driven website using PHP and MySQL.

 

I have come across a problem with my dates...

 

Basically I need to be able to insert a custom date into my database and then retrieve the date and format it in different ways. 

 

For example:

 

17 July 2007

Tuesday 17 July 2007

17/07/2007

 

Is this possible from one field with the date in MySQL?

What format do i need to insert the date into the database?

How can i format the date in these different formats? The one really stumping me is the Day of the week???

 

 

My next problem is I want to sort these fields, first by the month and then by the date of the month

 

i hope someone can shed some light

 

thanks in advance

John

 

[fenway]

Store as DATE(TIME), format pretty using DATE_FORMAT().

[johnnybravo]

Fantastic, got that working thanks!

 

I have another question now... I want to be able to filter the records... I would like to be able to limit the results to the first entry for each month, is that possible?

 

Code so far is:

SELECT ID, DATE_FORMAT(Date, '%W %D %M %Y') as Date from tbltest WHERE DATE_FORMAT(Date, '%j') > '$CurrentDate' ORDER BY DATE_FORMAT(Date, '%j') ASC LIMIT 5

 

thanks again for your help so far

John

[fenway]

I caution you against using a reserved keyword -- Date -- for your field name.. you're getting away with it because it's inside an expression.  Pick another name for your column.

 

Also, I find it easier to use DAYOFYEAR() rather than the corresponding date_format() for clarity -- though I'm not sure why you just don't use a proper date here, and set $currentDate appropriately -- that way, mysql can use any available index on this column.  Same goes for the order by.

 

I have another question now... I want to be able to filter the records... I would like to be able to limit the results to the first entry for each month, is that possible?

 

Sure, there are a few ways to do that, but it's not trivial -- you'll need to group by each month, but also by year, too.