MySQL exact equivalent of the PHP "time()" function

[roparzhhemon237]

 I’m translating some mixed PHP/MySQL code into pure MySQL code.
  Now I'm having a problem about how to translate PHP's time() function.

  I’m not sure how to choose between NOW(), CURDATE(), CURTIME(), and several others.
 
  I tried NOW(), but to my surprise it outputs today’s year (i.e. 2014) instead of the Unix integer timestamp I'm looking for. And this behaviour seems contray to the MySQL manual.
 
  What should I do ?


 

[Barand]

Did you look at UNIX_TIMESTAMP() in the manual?

[Zane]

SELECT UNIX_TIMESTAMP( datetime field);

This will cause your database to work harder though, especially if you have thousands of records.  Mysql's DATETIME datatype is the better alternative, it will allow you to perform complex date calculations much faster. otherwise you will have to convert all UNIX TIMESTAMPs to a mysql date format before performing any kind of the date functions provided by MySQL.

Granted, though, NOW() will give you the server time, while the UNIX Timestamp will give you seconds since the epoch.

[Barand]

In which way is NOW() behaving contrary to the manual?

 

NOW([fsp])

Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.

[roparzhhemon237]

Did you look at UNIX_TIMESTAMP() in the manual?

 

That works fine, thanks.

[roparzhhemon237]

SELECT UNIX_TIMESTAMP( datetime field);

This will cause your database to work harder though, especially if you have thousands of records.  Mysql's DATETIME datatype is the better alternative, it will allow you to perform complex date calculations much faster. otherwise you will have to convert all UNIX TIMESTAMPs to a mysql date format before performing any kind of the date functions provided by MySQL.

Granted, though, NOW() will give you the server time, while the UNIX Timestamp will give you seconds since the epoch.

 

 

I use this function everytime a new post is created in my forum. As all the posts are not posted at the same time, this looks reasonable to me.

[roparzhhemon237]

In which way is NOW() behaving contrary to the manual?

 

The manual says the format should be either 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.

The format I get is just YYYY. For some (unknown to me) reason, only the first four character or digits are retained.

[kicken]

The format I get is just YYYY. For some (unknown to me) reason, only the first four character or digits are retained.

Probably as a result of how you are using it. If you store the value in mysql in the 'YYYY-MM-DD HH:MM:SS' format but then type cast it to an INT in PHP after selecting it, you would observe this behavior.

 

$value = '2014-10-30 12:33:00'; //As would be returned by a query
$date = (int)$value;
var_dump($date); //int(2014);

[kicken]

I use this function everytime a new post is created in my forum. As all the posts are not posted at the same time, this looks reasonable to me.

As mentioned, using UNIX_TIMESTAMP with an INT column adds some extra processing to the work that needs to be done. It also makes the data less readable if you need to get in and view DB records directly. For example say you are debugging some code and need to browse the DB data. Which would you rather see in the date field: '1414690620' or '2014-10-30 13:37:00'?

 

The best solution when it comes to storing the current datetime into a database is to use a DATETIME field, and use the function UTC_TIMESTAMP(). This gives you a readable format that can be easily manipulated with mysql's date and time functions. The value is also unaffected by the server's timezone setting and is always stored in UTC time meaning it is easy to convert between timezones should you want to do that (such as let users customize their timezone).

Edited October 30, 2014 by kicken