Mysql DATETIME

[electronik]

I dont really have a problem, but I want to make my code faster and prettier.
I have some tables with DATETIME in it.
They have the std format %Y-%m-%d %H:%i:%s
I need them to be in %d.%m.%Y %H:%i format.

I tried to fix it with the server variable "datetime_format".
After reading a lot in mysql doc I found out that:

datetime_format
This variable is unused.

http://dev.mysql.com/doc/refman/5.0/...variables.html

So Im using

    DATE_FORMAT(date,'%d.%m.%Y %H:%i') //on SELECT
    STR_TO_DATE(insert,'%Y-%m-%d %H:%i:%s') //on INSERT  

on every query.

After sleeping a night over it I had an idea... Is it possible to write some INSERT/SELECT/UPDATE/ trigger that changes format on every field that is DATETIME?
Or does someone have another idea?

[Atli]

Hey.

DATETIME fields aren't actually strings in any specific format. The actual date and time information is stored as numbers, and then converted to the "Y-m-d H:i:s" format when you retrieve them.

Which means, no. You can't change the format of a DATETIME field until you fetch the data. Then you can use the DATE_FORMAT function to specify in which format you would like them to be converted before they are returned.

If you wanted to store the date in a particular format, then you would have to store it as a string, which is a horrible idea.

In any case, this sort of manipulation of the data should ideally be done on the front-end, or in the SQL queries, but it should never be done to the data itself. The data should always be in neutral form; not specific to any one front-end.

[electronik]

I dont want to store the datetime in a diffrent format...
I want to edit them on every INSERT UPDATE or SELECT
INSERT 20.01.2010 --> 2010-01-20
SELECT 2010-01-20 --> 20.01.2010
so that I dont have to change it on every query.