MySQL question

[catalinus]

I have the following situation: each time i insert a row into a table, i want the date of the operation to be inserted in a separated field(called process_date). I have set the process_date field to be a timestamp type, and its defalut value to be now(), but the date inserted into it is like 20010806231545. I need this date to be printed with separators, 2001-08-06 23:15:45 instead of 20010806231545. Could you help me with an answer here?Thanks

[crislewis]

use this function:

Function sql_date_and_time($sql_date) {

$read_date = substr($sql_date,6,2)."/";
$read_date .= substr($sql_date,4,2)."/";
$read_date .= substr($sql_date,0,4)." at ";
$read_date .= substr($sql_date,8,2).":";
$read_date .= substr($sql_date,10,2).":";
$read_date .= substr($sql_date,12,2);

return $read_date;

Assuming you have the data from the database in $row_data and the field is called process_date print/display the date/time with

echo sql_data_and_time($row_data["process_date"]);

This assumes you have used mysql_fetch_array to populate $row_data

Cheers,

Cris

[Anon]

Yikes! That looks like a hell of a scary way of achieving something relatively easy.

Instead of doing "SELECT process_date from table;", why not do "SELECT DATE_FORMAT(process_date, '%Y-$m-%d %r')"

DATE_FORMAT is designed to do exactly what you want. Check 7.4.11 in the MySQL manual.

Cheers
Dom

[[deleted]]

Note: date_format() is for output only.

Do not use date_format() if you want to compare dates inside your query.
I've seen people use date_format() to get the name of a month to compare it to a string. It doesn't get slower than that.

[catalinus]

Thanks, it worked !