how to change timestamp format in mysql

Is it possible to change the current 24 hr format like 22:16:08 to something like 10:16:08 in mysql? I want to change how these va开发者_如何学Golues are saved. Is it possible or I will just let php handle things for me?

Don't change the way how these values are saved. Change the way they are output.

See TIME_FORMAT()

In your case, this would show a TIME field as 10:16:08 PM:

SELECT TIME_FORMAT(timefield, "%l:%i:%s %p") AS date_formatted;

You should always store date/time values in their native format, which in MySQL is the 24hr format. You can change how they're retrieved with

SELECT DATE_FORMAT(somedatetimefield, 'format string here')
FROM table

where the format string options are defined here

If doing that manually for every query is a problem, you can always create a view to do it for you automatically.

You really shouldn't change how the data is saved in MySQL. Instead you should only present it differently. You can use PHP's date function to format the date in anyway you want. This is a huge advantage because you are separating how the data is saved and how the data is presented.

Using DATEFORMAT you can save/fetch the date how you wish (much like PHP date()'s syntax)

Like Pekka said: Don't change the way how these values are saved.

You can also easily handle this in php using date()

like date("hh:ii:ss") This puts out the format you used in your example. (which is 12-hours format and leading zeros.)