MySQL date format

[bysonary]

I am in the process of thinking about doing something but have kinda hit a mental brick wall.

I want to be able to insert stuff into a database, thats fine yeah, but how would I go about sorting it by the date it was submitted?

I am in the UK where the date format is DD/MM/YYYY and not MM/DD/YYYY, I know that mysql takes the date in the US format of MM/DD/YYYY, is there anyway to change this and successfully sort the data by the date it was submitted, e.g. have the oldest at the bottom and newest at the top?

any ideas, help or references would be appreciated.

Thanks

Chris

[jatar_k]

just store a timestamp, you can then sort easily and can format it however you like for display

[camilord]

yeah.. i usually use mktime() hehehee.. :)

[phranque]

this is the mysql reference on date and time types [dev.mysql.com].
this is the mysql reference on date and time functions [dev.mysql.com].
with these functions you can accomplish just about any time slicing and formatting you could imagine.
the storage format of all date and time types makes them naturally sortable.

for example:
SELECT stuff, DATE_FORMAT(submitted_date, '%d/%m/%Y') AS uk_date FROM mytable ORDER BY submitted_date DESC;

note: as of mysql 5.1 there are some changes you should be aware of regarding language, character set and collation sequence for date_format() results.

[phranque]

jatar_k:

just store a timestamp, you can then sort easily and can format it however you like for display

the timestamp column will typically update whenever an insert or update occurs.
this is the easy way to track the last change.
to specifically control the submitted date, you should use a datetime column and mktime() function on insert as camilord pointed out.

[jatar_k]

ah, I didn't say make a TIMESTAMP column, I said "store a timestamp"

I never use TIMESTAMP columns

[phranque]

ah, I didn't say make a TIMESTAMP column, I said "store a timestamp"

my bad.
i would have called that a "time stamp".

[bysonary]

Nice cheers for all the suggestions, bit of readin for me, thanks again webmasterworld and your members.