Alternative for MySQL UNIX_TIMESTAMP()

In 2038, 32-bit systems and 32-bit code has a problem. It’s well documented online, so if you do not know much about it, check it out on Wikipedia then come back and continue reading here. Even if you are running MySQL on a 64-bit machine, MySQL is not—at least not fully.

Ideally you would want to use the scripting language running your application to do the conversion. For example, place your date string from your result and run it through strtotime() or the DateTime class in PHP. But let’s assume that you are unable to do that for whatever crazy reason and you need to have the date and time as an Unix timestamp in your query results. What do you do?

Assuming we still use MySQL in 2038 and the problem isn’t fixed by then or you are working with future dates that may go into or past 2038 before we get there, there is a way around it. If you use:

TO_SECONDS(`datetime`)-62167219200+TO_SECONDS(UTC_TIMESTAMP())-TO_SECONDS(NOW())

instead of:

UNIX_TIMESTAMP(`datetime`)

in your MySQL queries, you will get exactly the same Unix timestamp, but future ready. It will continue to work in and past 2038.

Just execute this query to see it demonstrated:

SELECT
    `datetime`,
    UNIX_TIMESTAMP(`datetime`) AS `timestamp`,
    TO_SECONDS(`datetime`)-62167219200+TO_SECONDS(UTC_TIMESTAMP())-TO_SECONDS(NOW()) AS `timestamp_alt`
    # NOTE: 62167219200 == TO_SECONDS('1970-01-01 00:00:00')
FROM
    (
        (
            SELECT NOW() AS `datetime`
        )
        UNION ALL
        (
            SELECT DATE_ADD(NOW(), INTERVAL 30 YEAR) AS `datetime`
        )
    ) `datetest`

This works in MySQL 5.5 or later. If you are stuck using a version older than 5.5 or need to be backwards compatible, it’s a little more complicated:

((TO_DAYS(`datetime`)-719528+TO_DAYS(UTC_DATE())-TO_DAYS(CURDATE()))*86400)+TIME_TO_SEC(TIME(`datetime`))+TIME_TO_SEC(UTC_TIME()-CURTIME())
# NOTE: 719528 == TO_DAYS('1970-01-01 00:00:00')
# NOTE: 86400 == seconds in a day

Now, go forth and be ready for the 2038 problem… at least in your MySQL queries. If you find any place for improvement, please feel free to share your thoughts in the comments. Thanks for reading!

PHP: Improving print_r() with print_pre() Wrapper Function

When you need a simple debugging function and print_r() is just not enough, you need a good wrapper function. You need print_pre().

Most print_pre() functions out there just wrap the print_r() output with the <pre> tag. Useful, but not that useful. This print_pre() function has evolved over the time I have used it. I have used it across multiple jobs. Where it is now is good, anything added to it would be for specialized cases.

This print_pre() function does a bit more than just wrap the print_r() with <pre> tags. Sometimes you may not be able to view any output easily, so it may output to a file if you so desire. It can be used in place of any print_r() as it may be used exactly the same. Another cool feature is word wrapping, which basically does away with the <pre> tag and wraps it in a <p><tt> combo.

function print_pre ($expression, $return = false, $wrap = false)
{
  $css = 'border:1px dashed #06f;background:#69f;padding:1em;text-align:left;';
  if ($wrap) {
    $str = '<p style="' . $css . '"><tt>' . str_replace(
      array('  ', "\n"), array('&nbsp; ', '<br />'),
      htmlspecialchars(print_r($expression, true))
    ) . '</tt></p>';
  } else {
    $str = '<pre style="' . $css . '">'
    . htmlspecialchars(print_r($expression, true)) . '</pre>';
  }
  if ($return) {
    if (is_string($return) && $fh = fopen($return, 'a')) {
      fwrite($fh, $str);
      fclose($fh);
    }
    return $str;
  } else
    echo $str;
}

The print_pre() function has simple usage:

mixed print_pre (mixed $expression [, mixed $return = false [, bool $wrap = false ]] )

The second parameter, return, works just like the return parameter on print_r() except when a non-empty string is passed. If a string is passed to the return parameter, it will be treated as a file path to save the output to and will continue to return the output. The last parameter, wrap, is rather self explanatory; it will allow long lines to break and the text will not be preformatted if set to TRUE.

Everybody likes examples, so here are a few for you:

// Let us assume print_pre() and $obj were defined before this point

print_pre($obj);
// the output is simply printed

$string = print_pre($obj,true);
// $string contains the output

echo print_pre($obj,'debug.htm',true);
// the output allows wrapping, is saved to debug.htm and then printed

And that is the print_pre() function. Ok, my print_pre() function. Enjoy!