Solomon Closson Solomon Closson - 1 year ago 158
MySQL Question

Convert DECIMAL(16,4) into DATETIME MySQL

I need to convert a timestamp that gets stored as DECIMAL(16, 4) in the database to DATETIME when pulling it out from the database in a select, if possible.

The way it gets stored in the database is as follows:

$time = function_exists('microtime') ? microtime(true) : time();
$submit_time = number_format($time, 4, '.', '');

goes into database with column

Is it possible to extract this value and
it to a
value when pulling it from the database? The reason I would like to convert it to
is because it would be easier to manage when searching dates that are being outputted using pagination.

Been looking at the MySQL
Function, and have tried the following, but it outputs empty content:

SELECT CONVERT(submit_time, DATETIME) AS datetime

Any help is greatly appreciated ofcourse!

Please note, I've seen the Answer that is tagged to this question, and it does not help my situation. Firstly, all answers, save 1, are returning PHP code that converts it, and Secondly the supposed answer in SQL for CONVERT does not work in MySQL for a number of reasons, 1 being that in SQL, looks like there are 3 parameters for CONVERT, but in MySQL, only 2. Also, CONVERT in MySQL either works differently, or is not the correct function and that answer is wrong.

Answer Source

Depends from needed accuracy

select from_unixtime(round(submit_time)) as datetime -- 2012-04-10 09:00:26


select from_unixtime(submit_time) as datetime -- 2012-04-10 09:00:25.5900

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download