0

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?

0
2

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.

5
  • How might I add conditions, again, in a legible format versus this 'unix time,' so that I can fine log entries that occur between specific dates? Mar 12 '20 at 4:41
  • 2
    Like SELECT ... FROM ... WHERE from_unixtime(double_field) BETWEEN '2010-02-03' AND '2015-12-03'? If that's off track I'm not sure what you mean.
    – danblack
    Mar 12 '20 at 5:00
  • Beware though that this query won't be sargable! If you're using MySQL 8, then, depending on your circumstances, a generated field might be an idea?
    – Vérace
    Mar 12 '20 at 7:05
  • 2
    Then flip it around -- ... WHERE double_field BETWEEN UNIX_TIMESTAMP('2010-02-03') AND UNIX_TIMESTAMP('...')
    – Rick James
    Mar 13 '20 at 3:52
  • @user2461674 One more REASON to use datatype of datetime, it would be legible, you can select between start_date and end_date without going around the world and an index will reduce overhead. And your application will not go dead in February, 2038 due to range limit of timestamp. Mar 18 '20 at 19:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.