How to convert a time stamp to a date in mysql

[ballhogjoni]

I want to convert my timestamp to the date of the timestamp. This is a date that is in my db 1209022076, the problem is that this doesn't show me a specific date.

 

Here is my query:

 

SELECT DISTINCT t_o.email, t_o.first_name, t_o.last_name, t_o.b_zip, t_o.date, t_o.fulfill_date

FROM travel_orders AS t_o JOIN travel_affiliates AS t_a ON t_o.affiliate_id = t_a.id JOIN travel_affiliate_platforms AS t_a_p ON t_a.affiliate_platform_id = t_a_p.id

WHERE t_a_p.id = 11

AND t_o.status IN ('new','printed', 'partially_charged', 'charged', 'filled', 'processed', 'held', 'charge_held')

AND t_o.email IS NOT null

AND t_o.email != '';

[realjumper]

http://php.net/date

[ballhogjoni]

I need a mysql function that does that.

[DeanWhitehouse]

instead of using a mysql timestamp, y not insert a php date variable into the database

e.g.

$time_stamp = date("d.m.Y");

$date = $time_stamp;

[PFMaBiSmAd]

See the mysql FROM_UNIXTIME(unix_timestamp,format) function. You can convert and format to anything you want at the same time.

 

[Barand]

instead of using a mysql timestamp, y not insert a php date variable into the database

e.g.

$time_stamp = date("d.m.Y");

$date = $time_stamp;

 

dmy format in a database is as much use as a chocolate teapot.

[DeanWhitehouse]

it works fine for me

[Barand]

It will if all you do is store it and print it.

 

But it's impossible to do less-than or greater-than comparisons, therefore you can't search on it or sort on it.

None of the MySQL or PHP date/time functions work with it so you can't reformat it or make use of the dozens of other functions (except MySQL STR_TO_DATE() which will change it to a usable format that you should be using in the first place)