Insert Unix Time into MySQL?

[doubledee]

What needs to be changed to make this work...

// Build query.
$q2 = "UPDATE member
		SET logged_in=?,
			last_activity=time(),
			updated_on=NOW()
		WHERE id=?
		LIMIT 1";

 

Apparently PHP doesn't like time() 

 

 

Debbie

 

[marcus]

Sorry I confused myself.

 

You can use the time function no problem.

 

$q2 = "UPDATE member SET last_activity = '".time()."'";

[doubledee]

Sorry I confused myself.

 

You can use the time function no problem.

 

$q2 = "UPDATE member SET last_activity = '".time()."'";

 

You lost me.

 

Why doesn't my code work?

 

 

Debbie

 

[marcus]

Are you getting any error? What is the value in the table updating to?

[doubledee]

Are you getting any error? What is the value in the table updating to?

 

Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, boolean given in /Users/user1/Documents/DEV/++htdocs/05_Debbie/members/log_in.php on line 179

 

 

Debbie

 

 

[marcus]

Show us more code.

[doubledee]

Show us more code.

 

// ************************
// Update Member Record.	*
// ************************
$loggedIn = TRUE;

// Build query.
$q2 = "UPDATE member
		SET logged_in=?,
			last_activity=time(),
			updated_on=NOW()
		WHERE id=?
		LIMIT 1";

// Prepare statement.
$stmt2 = mysqli_prepare($dbc, $q2);

// Bind variables to query.
mysqli_stmt_bind_param($stmt2, 'si', $loggedIn, $memberID);

// Execute query.
mysqli_stmt_execute($stmt2);

 

 

Debbie

 

[marcus]

The reason for your error is because the mysqli_prepare statement is returning false. It could either be because a) Your connection is invalid or b) The query is wrong.

 

Try making this change to your query:

 

$q2 = "UPDATE member SET logged_in = ?, last_activity = UNIX_TIMESTAMP(NOW()), updated_n = NOW() WHERE id = ? LIMIT 1";

[doubledee]

The reason for your error is because the mysqli_prepare statement is returning false. It could either be because a) Your connection is invalid or b) The query is wrong.

 

Try making this change to your query:

 

$q2 = "UPDATE member SET logged_in = ?, last_activity = UNIX_TIMESTAMP(NOW()), updated_n = NOW() WHERE id = ? LIMIT 1";

 

That seems to have fixed things, but can you help me understand what this means from the MySQL Manual...

If called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' UTC) as an unsigned integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' UTC.

 

I don't follow what UNIX_TIMESTAMP() would do.

 

Also, why did we have to switch from time() to now()??

 

Thanks,

 

 

Debbie

 

 

[marcus]

Well look at it this way. If you don't escape time() the query is going to treat it as string(6) "time()" instead of the Unix timestamp we were looking for. Using the MySQL methods UNIX_TIMESTAMP and NOW() we don't have to worry about them being parsed as strings because MySQL will recognize them and treat them as methods instead of strings.

 

You could continue using time() if you wanted to, like:

$q2 = "UPDATE member SET logged_in = ?, last_activity = '".time()."', updated_on = NOW() WHERE id = ? LIMIT 1";

 

Hope that clears things up.

[doubledee]

Well look at it this way. If you don't escape time() the query is going to treat it as string(6) "time()" instead of the Unix timestamp we were looking for. Using the MySQL methods UNIX_TIMESTAMP and NOW() we don't have to worry about them being parsed as strings because MySQL will recognize them and treat them as methods instead of strings.

 

You could continue using time() if you wanted to, like:

$q2 = "UPDATE member SET logged_in = ?, last_activity = '".time()."', updated_on = NOW() WHERE id = ? LIMIT 1";

 

Hope that clears things up.

 

So even though my query is being created in PHP, I need to use MySQL Functions (e.g. UNIX_TIMESTAMP() and NOW()) in order to get things to work, right?

 

On a side note, I am wondering if there is a better way to do what you just helped me with.

 

Rewind...

 

I am displaying User Comments below an Article.  Each Commenter has either a Green, Yellow or Gray indicator to show if they are Online, Idle or Offline.  This is based on the difference between "last_activity" and the Current Time.

 

Would it be better to do the math using PHP or MySQL?

 

 

Debbie

 

[marcus]

Since time is a method it can't just be called in the middle of a variable unless escaped. You could have assigned the time method to a variable ($time = time()) and used $time in your query if you wanted. You aren't restricted to using the UNIX_TIMESTAMP() and NOW() MySQL methods, you can use the PHP time() method, but you have to escape it or assign it to a variable for it to work in your query.

 

You could do the math in MySQL or PHP, it's really up to you.

 

SELECT (UNIX_TIMESTAMP(NOW()) - last_activity) as lastActive FROM member

 

Then loop through the results.

 

while($row = mysqli_fetch_assoc($query){
     $indicator = ($row['lastActive'] < 50) ? "green" : ((in_array($row['lastActive'], range(50,100))) ? "idle" : "offline");
     // 0-49 = green, 50-100 = idle, > 100 = offline, you can change those values to correspond with however you want it to be
     // maybe Online = last 15 minutes, idle = 15-30 minutes, offline = > 30 minutes
     // lastActive will return in seconds so 15 minutes = 900 seconds
}

[doubledee]

Since time is a method it can't just be called in the middle of a variable unless escaped. You could have assigned the time method to a variable ($time = time()) and used $time in your query if you wanted. You aren't restricted to using the UNIX_TIMESTAMP() and NOW() MySQL methods, you can use the PHP time() method, but you have to escape it or assign it to a variable for it to work in your query.

 

You could do the math in MySQL or PHP, it's really up to you.

 

SELECT (UNIX_TIMESTAMP(NOW()) - last_activity) as lastActive FROM member

 

Then loop through the results.

 

while($row = mysqli_fetch_assoc($query){
     $indicator = ($row['lastActive'] < 50) ? "green" : ((in_array($row['lastActive'], range(50,100))) ? "idle" : "offline");
     // 0-49 = green, 50-100 = idle, > 100 = offline, you can change those values to correspond with however you want it to be
     // maybe Online = last 15 minutes, idle = 15-30 minutes, offline = > 30 minutes
     // lastActive will return in seconds so 15 minutes = 900 seconds
}

 

I read somewhere that using the Unix Time is better because it is not impacted by Time Zones?! 

 

But for this context, that shouldn't matter, right?

 

Don't you agree with me that storing an English-readable Date/Time in my Database is better than the # of seconds from the Unix Epoch?!

 

Assuming you do, then would I want to use DATETIME or TIMESTAMP format??

 

Thanks,

 

 

Debbie

 

 

[marcus]

Personally I would just store it as the seconds. But if you wanted to use either DATETIME or TIMESTAMP I would use TIMESTAMP.

 

It's easier to calculate the time differences using the actual seconds. Going from TIMESTAMP to seconds and then finding the time difference requires that conversion.

[kicken]

So even though my query is being created in PHP, I need to use MySQL Functions (e.g. UNIX_TIMESTAMP() and NOW()) in order to get things to work, right?

 

As far as PHP knows or cares, your query is just a string, and the string "time()" has no special meaning.  As far as MySQL knows or cares, your query could have come from anywhere and it has no idea wtf time() is so it throws an error.

 

So yes, in your SQL querys you have to either a) stick to MySQL's functions or b) break out of the string and use the concatenation operator to insert the results of calls to PHP functions.

 

 

Don't you agree with me that storing an English-readable Date/Time in my Database is better than the # of seconds from the Unix Epoch?!

 

You should store a date that is in the proper format for MySQL to understand it as a DATETIME or TIMESTAMP column value.  When you store a date as one of those values, then mysql knows how to handle it correctly and will allow you to do math or other operations on it easily.

 

If for some reason you can't / won't store it as a DATETIME or TIMESTAMP, storing as an INT, using the standard unix timestamp format (seconds since epoc) is your next best thing as you can still do math with it pretty easy and it is fairly well supported.

 

What you should pretty much never do is store a string representation of a date in a VARCHAR column (ie, '5/2/2012'.  MySQL has no idea how to handle that and it just makes you have to work harder to do anything useful with it.

 

 

[doubledee]

You should store a date that is in the proper format for MySQL to understand it as a DATETIME or TIMESTAMP column value.  When you store a date as one of those values, then mysql knows how to handle it correctly and will allow you to do math or other operations on it easily.

 

I'm really confused on this entire topic, and getting more confused as the minute passes... 

 

First of all, what is the difference between DATETIME and TIMESTAMP??

 

I looked online but can't figure that out.

 

As such, for my purposes, why would I want to choose one versus the other?

 

I saw something online that said that the Unix Time is better because it is not affected by International Time Zones...

 

 

Next question, if I did use DATETIME or TIMESTAMP in MySQL, then how do I take those values and compare them to the Current Time so that my script can determine which "Online Indicator" to display?

 

Do I have to convert the DATETIME or TIMESTAMP to some PHP format?

 

Do I do the math in SQL?

 

A User falls into 3 categories:

 

- Online (last_activity < 15 minutes)

- Idle (15 minutes =< last_activity < 30 minutes)

- Offline (last_activity > 30 minutes)

 

Thanks,

 

 

Debbie

 

 

[kicken]

The DATE, DATETIME, and TIMESTAMP Types

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'

...

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

...

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC

 

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)

...

If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions.

 

 

Basically, a DATE type is just the day without any time component.  A DATETIME include a timecomponent as well.  Both of these types are stored in a manner that gives them a large range of values.  Neither of these types are altered in any way by a timezone setting

 

A TIMESTAMP is equivilent to a unix timestamp value and it is altered by timezone settings.

 

I saw something online that said that the Unix Time is better because it is not affected by International Time Zones...

 

Unix timestamps are the seconds since Jan 1 1970 @ midnight, UTC.  As such whenever you try and convert a date/time value to a unix timestamp (such as with strtotime) the date is first converted to the UTC timezone and then the seconds returned.  When you format the date, the offset of the current timezone is applied, then the date is formatted.

 

This makes timestamps useful as you can just alter the current timezone to convert a date between timezones.  You can handle this using DATETIME as well though, fairly easily even with php's [m=class.datetime]DateTime class[/m]

 

 

Do I have to convert the DATETIME or TIMESTAMP to some PHP format?

 

Do I do the math in SQL?

PHP's strtotime will understand the default format they are returned in.  You can however use mysql's DATE_FORMAT() function to put them into whatever display format you want from within the query and not have to bother with it in PHP. Doing things within the query when possible is usually most effecient. 

 

 

[doubledee]

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)

 

So it is now 4:19pm MST in Arizona and 11:18pm UTC.

 

So MySQL stores that in the database as 11:18pm UTC?

 

And MySQL displays the time as 4:19pm MST when it is output?

 

 

If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions.

 

Example?

 

 

I saw something online that said that the Unix Time is better because it is not affected by International Time Zones...

 

 

Unix timestamps are the seconds since Jan 1 1970 @ midnight, UTC.  As such whenever you try and convert a date/time value to a unix timestamp (such as with strtotime) the date is first converted to the UTC timezone and then the seconds returned.  When you format the date, the offset of the current timezone is applied, then the date is formatted.

 

You lost me.  You are saying that the Unix Seconds to Epoch reflect the number of seconds from current Time Zone to the Epoch?

 

 

This makes timestamps useful as you can just alter the current timezone to convert a date between timezones.[/quotes]

 

So if a TIMESTAMP is 4:26pm MST and I convert it to 2 hours ahead (i.e. 6:26pm MST), nothing will be lost?!

 

 

Do I have to convert the DATETIME or TIMESTAMP to some PHP format?

 

Do I do the math in SQL?

PHP's strtotime will understand the default format they are returned in.  You can however use mysql's DATE_FORMAT() function to put them into whatever display format you want from within the query and not have to bother with it in PHP. Doing things within the query when possible is usually most effecient.

 

No.  If I have "last_activity" stored as a DATETIME or TIMESTAMP, then when I am displaying Users, how do I compare the database values to the Current Time?

 

// ********************************
// Display Comments on Article.		*
// ********************************
while (mysqli_stmt_fetch($stmt2)){
	// Set Photo Label.
	$photoLabel = (empty($photoLabel) ? $username : $photoLabel);

	echo '<div class="post">';

	// ********************
	// Display User Info.	*
	// ********************
	echo '	<div class="userInfo">
				<a href="#" class="username">
					<strong>' . nl2br(htmlentities($username, ENT_QUOTES)) . '</strong>
				</a>';

	// Display User Online Status Here.	*

 

I was asking if I convert DATETIME or TIMESTAMP to something PHP can work with to find the Time Online, or if I should be doing that all in the Database?

 

Follow me?

 

 

Debbie

 

[cpd]

With regards to querying if you should store as a TIMESTAMP or a UNIX TIMESTAMP I would reccomend using a UNIX TIMESTAMP as I find it a little easier to go from UNIX TIMESTAMP  -->  TIMESTAMP using the gmdate() or date() functions.

 

If you were to do this just set the field to "integer" as already suggested. Alternatively, use the DATETIME format for your database and store as a TIMESTAMP.