CODEWITHSUNDEEP
phpmysqldatetime
Smith
Smith

Reputation: 5951

php and mysql date time difference

I did this in php to store the lastlogin in a mysql datetime column

date('Y-m-d h:i:s') //which then gave > '2014-01-04 08:00:56'

and then saved it

When I need to calculate users who have been online more than 20 minutes, I did this in mysql

SELECT * FROM `elc_users`  WHERE TIMESTAMPDIFF( MINUTE , lastlogin, NOW( ) ) <20

it didn't work, when I traced in mysql, Now() gave this > 2014-01-04 20:00:56

Now this wont let me get my 20 minutes logged in users.

How can I fix this?

EDIT

I inserted a record manually in phpMyAdmin, to be sure whthear its timezone issue in a timestamp column with default value current_timestamp, it gave 2014-01-05 06:48:21. I the ran this query in phpMyAdmin

SELECT created, NOW( )  `elc_users`

this gave 2014-01-05 18:53:53

Now i also exec the code below to check the global and session timezone, and i got phpMyAdmin

mysql> SELECT @@global.time_zone, @@session.time_zone;

which then gave SYSTEM | SYSTEM

Upvotes: 1

Views: 3340

Answers (4)

Smith
Smith

Reputation: 5951

I solved the problem by deleting the date column and created it again. this now recorded the time in the correct format

timestamp gave 2014-01-04 20:58:56, and NOW() gave 2014-01-04 20:58:56

thanks for all who contributed, this wasn't a timezone issue as @AbdulJabbarWebBestow mentioned

Thanks again

Upvotes: 0

Abdul Jabbar
Abdul Jabbar

Reputation: 5931

I don't think that this would be a timezone issue because PHP and MySQL both are running on same machine so both would be getting same time on calling current datetime. May be you can try this because I tried this on my machine as you said.

SELECT * FROM `elc_users` WHERE lastlogin < date_add(NOW(), INTERVAL -20 MINUTE)

Upvotes: 0

Manu
Manu

Reputation: 901

In order to avoid such problems, check the following things.

  1. Make sure that system time and system timezone of both the DB Server and the Application Server are same.
  2. Make sure that you have set the configuration of PHP and MySQL to take proper time-zones.
  3. Always use a single function to set update, insert rows that have a dateTime field in them. Personally, I suggest that you write a common function somewhere in your code, and whenever you need to get the current time, use that function, instead of using the MySQL's NOW()

Upvotes: 1

user3141031
user3141031

Reputation:

Like Filip said it's probably a timezone issue; you can change the timezone of your script to whatever timezone your MySQL is set to like this:

date_default_timezone_set('America/Los_Angeles');

Upvotes: 1

Related Questions