CODEWITHSUNDEEP
phpmysql
highfidelity
highfidelity

Reputation: 107

In MySQL, how to convert this string to a date?

I am creating a mysql db with a php frontend. The data it will use is extracted from another larger db and contains a date/time field which looks like this - 20120301073136 - which records when an event happened.

I understand that this might be a UNIX timestamp? Not sure.

I want to be show this field in the tables in my PHP webpage as a readable date and time -

ie something like 01-Mar-2012 07:31:36 or similar

Should I try and convert it with SQL command or let PHP format it? And, what is the code to do so?

BTW, it is important that I can sort the data (in SQL and in the PHP table) into date order - ie in the order that these events happened.

Thanks in advance for your help - Ive learnt a lot here already

J

Upvotes: 0

Views: 157

Answers (2)

Samson
Samson

Reputation: 2821

You should make use of the MYSQL DATE functions. Check the docs before asking simple questions. http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html.

Also you can sort the dates directly in your query using ORDER BY.

Upvotes: 1

juergen d
juergen d

Reputation: 204746

You can convert it to a datetime directly in your SQL query. Example:

select cast(20120301073136 as datetime)

You can also order that with no need to convert it since it is a number in the format YYYYMMDDHHmmss

select * from yourTable
order by yourDateTimeField

Upvotes: 1

Related Questions