Mysql – How to output a legible timestamp from “1583981745.335346” (stored in a ‘double’ field type) via a select statement

datatypesMySQLselecttimestamptype conversion

I have a table within a WordPress installation that stores timestamps in a format that I do not recognize, nor can I figure out how to translate it. In the MySQL database, it appears as "1583981745.335346". Within WordPress, in the plugin's log, this particular entry reads as follows:

03-11-2020
10:55:47.117 PM

Further examination of the table's structure via phpMyAdmin reveals that this particular column's type is 'double'.

I have done a ton of reading and testing and have even browsed the plugin's code, which can be found below, but I cannot for the life of me figure out how to translate this odd "1583981745.335346" format to a legible timestamp via a select statement.

https://github.com/WPWhiteSecurity/WP-Security-Audit-Log

Searching the files for the column name, 'created_on,' the closest thing I can find is the center of the section beginning on line 2012 of /classes/AlertManager.php:

    // Meta details.
    return array(
        'site_id'    => $site_id,
        'blog_name'  => $blog_name,
        'blog_url'   => $blog_url,
        'alert_id'   => $alert_id,
        'date'       => str_replace(
            '$$$',
            substr( number_format( fmod( (int) $created_on + $this->gmt_offset_sec, 1 ), 3 ), 2 ),
            date( $this->datetime_format, (int) $created_on + $this->gmt_offset_sec )
        ),
        'code'       => $const->name,
        'message'    => $occurrence->GetAlert()->GetMessage( $occurrence->GetMetaArray(), array( $this->plugin->settings, 'meta_formatter' ), $occurrence->_cachedmessage ),
        'user_name'  => $username,
        'user_data'  => $user_id ? $this->get_event_user_data( $username ) : false,
        'role'       => $roles,
        'user_ip'    => $ip,
        'user_agent' => $ua,
    );

Another clue is the following, found on line 62 of /classes/Loggers/Database.php:

$occ->created_on  = is_null( $date ) ? microtime( true ) : $date;

I have not been able to figure out what to do with this information, though, unfortunately. The ultimate goal is to be able to query this field (along with others, including in related tables) using a select statement and output an actual timestamp. Is this possible?

Best Answer

FROM_UNIXTIME can help.

select from_unixtime(1583981745.335346)

2020-03-12 02:55:45.335346

ref: fiddle

This is a datetime type so it can be handled as such in PHP.