UNIX_TIMESTAMP usage query

Question

in my php5 code i want to use the UNIX_TIMESTAMP function to convert a datetime to a unix timestamp without using php code to convert as i believe this way is quicker but how do you then get the unix timestamp out of the rowset ie how to index into the rowset to get the integer unix timestamp out

Below is a summary of the database schema

age   int(5) 
user  varchar(256)    
created  timestamp   CURRENT_TIMESTAMP 

$query="SELECT user, UNIX_TIMESTAMP(created) FROM  accounts WHERE age='$age'" ;
$result = mysql_query($query)  or die ('Error querying domain');

while($row = mysql_fetch_array($result))
{
 $callerid = $row['callerid'];
 $created_ts = $row['created'];
 etc....

Sarfraz · Accepted Answer · 2010-08-08 09:00:44Z

4

Or use AS clause to give your column more descriptive name like this:

UNIX_TIMESTAMP(created) AS ucreated

And you can get it like:

while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

  $created_ts = $row['ucreated'];
............

More Info:

http://dev.mysql.com/doc/refman/5.0/en/select.html

edited Aug 8, 2010 at 9:00

answered Aug 8, 2010 at 8:52

Sarfraz

384k81 gold badges561 silver badges613 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mchl · Accepted Answer · 2010-08-08 08:52:05Z

2

Use alias SELECT user, UNIX_TIMESTAMP(created) AS created FROM accounts

answered Aug 8, 2010 at 8:52

Mchl

62.4k9 gold badges121 silver badges121 bronze badges

Comments

Matthew Flaschen · Accepted Answer · 2010-08-08 08:50:10Z

1

By default (MYSQL_BOTH), mysql_fetch_array gives you both numeric and associative indices. So you can do:

$created_ts = $row[1];

Or, give it a name:

$query="SELECT user, UNIX_TIMESTAMP(created) AS created_ts FROM  accounts WHERE age='$age';";
// ...
$created_ts = $row['created_ts'];

Also note that if age comes from user input you should use mysql_real_escape_string before concatenating it. Or switch to a framework (like PDO or MySQLi) that supports prepared statements.

answered Aug 8, 2010 at 8:50

Matthew Flaschen

286k53 gold badges523 silver badges554 bronze badges

Collectives™ on Stack Overflow

UNIX_TIMESTAMP usage query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related