Need to translate datetime from 10 digit to standard date

[systemsgotyou]

Hello, I need the php way to take this kind of mysql datetime field:

 

1390146482

 

and make it into this kind:

 

19/01/2014

 

 

I already know how to get and insert, I just need some help with translation. This datetime thing is confusing!

[ginerjm]

You can read up on DATE_FORMAT function of MySQL (its online) or you can use the date function of php to translate it (its in the php manual)

[Barand]

In SQL you would need FROM_UNIXTIME() function first, then you can format it

mysql> SELECT DATE_FORMAT(FROM_UNIXTIME(1390146482), '%d/%m/%Y') as date;
+------------+
| date       |
+------------+
| 19/01/2014 |
+------------+

Edited January 19, 2014 by Barand

[systemsgotyou]

I want to copy it from one field (the 10 digit version) and then copy it to another field as the date version like above.

 

I am 99% sure it has to do with the datetime() function. Any help?

[Ch0cu3r]

Not sure why you'd want to save the formatted date. Raw unformatted data should be stored, you'd format the data to your chosing when you go to display it.

 

If you want to save the formatted date to all records then I guess you can use an update query

UPDATE table SET formatted_date_field = DATE_FORMAT(FROM_UNIXTIME(non_formated_date_field), '%d/%m/%Y');

Edited January 19, 2014 by Ch0cu3r

[Barand]

When storing dates use column type DATE, format YYYY-MM-DD.

 

DD/MM/YYYY is as much use in a database as a chocolate teapot. You can't compare dates, sort them or use date time functions with that format.