Jump to content

time() capturing wrong time


ajetrumpet

Recommended Posts

I have this:

    $sql = mysqli_query($conn, "SELECT ip
                                     , page
                                     , CASE WHEN referrer = ''
                                            THEN 'N/A'
                                            ELSE referrer
                                       END as referrer     
                                     , DATE_FORMAT(date, '%m/%d/%y') as date
                                     , TIME_FORMAT(time, '%T') as time
                                FROM tblTraffic 
                                ORDER BY date DESC, time DESC");

most of my traffic report contains the correct times, my time, which is CST in the USA.  but some records are off by a lot.  Does the time() function capture the time of the actual location from which the visitor is coming from?  if so, what could I do to return my timezone for any visitor?  w3 schools and php.net don't mention this.

Link to comment
Share on other sites

11 minutes ago, ajetrumpet said:

Does the time() function capture the time of the actual location from which the visitor is coming from?

PHP's time() function returns a Unix timestamp. It is the same value for everyone.

11 minutes ago, ajetrumpet said:

if so, what could I do to return my timezone for any visitor?  w3 schools and php.net don't mention this.

You have to know something about where your user lives. With only PHP you can use geolocation on their IP address to get a good approximation. With Javascript you can get their "timezone" (UTC offset) with the Date object, which does not tell you their location.

Link to comment
Share on other sites

6 hours ago, requinix said:

You have to know something about where your user lives. With only PHP you can use geolocation on their IP address to get a good approximation. With Javascript you can get their "timezone" (UTC offset) with the Date object, which does not tell you their location.

no no, I'm not interested in capturing THEIR time, I want to store my own!  central daylight time, Chicago IL time

Berand,

the time is stored in the DB the same way it is listed on the report.

Link to comment
Share on other sites

12 minutes ago, ajetrumpet said:

no no, I'm not interested in capturing THEIR time, I want to store my own!  central daylight time, Chicago IL time

Then what did you mean when you asked

6 hours ago, ajetrumpet said:

Does the time() function capture the time of the actual location from which the visitor is coming from?

 

Link to comment
Share on other sites

Then

Quote

the time is stored in the DB the same way it is listed on the report.

That's not what Barand was asking for. The question is about the data type: DATETIME, TIMESTAMP, VARCHAR, whatever.

On top of that is the question of how the values are getting to the database. You mentioned time() - is that what you're using? Or date()? Maybe MySQL's NOW()?

 

And please, I know you think it's funny, but it's off-topic. It doesn't need to be here. I mean, it's not even related to programming. Maybe check out Miscellaneous?

Link to comment
Share on other sites

4 hours ago, requinix said:

The question is about the data type: DATETIME, TIMESTAMP, VARCHAR, whatever.

On top of that is the question of how the values are getting to the database. You mentioned time() - is that what you're using? Or date()? Maybe MySQL's NOW()?

the data type in the DB field is DATE.  I'm using time() nested inside the TIME_FORMAT()  function in the SQL statement that adds the records to the database, as I showed in my original post.  And that is Berand's code not mine.

Edited by ajetrumpet
Link to comment
Share on other sites

Try this...

Add a TIMESTAMP type column to your table

 `logged` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

then use the time portion of this column in your query instead of your current time column. (You don't need to change the INSERT query as it will update itself automatically)

$sql = mysqli_query($conn, "SELECT ip
                                     , page
                                     , CASE WHEN referrer = ''
                                            THEN 'N/A'
                                            ELSE referrer
                                       END as referrer     
                                     , DATE_FORMAT(date, '%m/%d/%y') as date
                                     , TIME_FORMAT(logged, '%T') as time
                                FROM tblTraffic 
                                ORDER BY date DESC, time DESC");

 

Edited by Barand
Link to comment
Share on other sites

39 minutes ago, Barand said:

Try this...

Add a TIMESTAMP type column to your table


 `logged` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

then use the time portion of this column in your query instead of your current time column. (You don't need to change the INSERT query as it will update itself automatically)


$sql = mysqli_query($conn, "SELECT ip
                                     , page
                                     , CASE WHEN referrer = ''
                                            THEN 'N/A'
                                            ELSE referrer
                                       END as referrer     
                                     , DATE_FORMAT(date, '%m/%d/%y') as date
                                     , TIME_FORMAT(logged, '%T') as time
                                FROM tblTraffic 
                                ORDER BY date DESC, time DESC");

i don't think that's what you meant, cuz now all of the records are showing 03:15:50 for the timestamp column.  i changed the query too, and all the records come out at that time too.

 

Link to comment
Share on other sites

All existing records will have the timestamp auto updated to the time the column was added.

When new records are added that timestamp column will be the time added.

You could set that column to zero date (for currently existing records) so only new records get a time stamp. 

UPDATE tblTraffic SET logged = 0;

Then you will know which time to use - the timestamp if non-zero or the time column if timestamp is zero.

SELECT TIME_FORMAT(IF(logged=0, time, logged), '%T') as time

EDIT:

Alternatively, copy the date and time columns' values into the timestamp column

Edited by Barand
Link to comment
Share on other sites

15 minutes ago, Barand said:

All existing records will have the timestamp auto updated to the time the column was added.

When new records are added that timestamp column will be the time added.  oh by the way, in another thread you were telling me that petrol is expensive there in england.  my friend from poland says that the most expensive in the world is england and poland, and the cheapest is the UAE, where all the oil comes from.  true?

 

ok i'll wait until another bot crawls the site and see if this new column does the trick.  i'll keep you updated.

Edited by ajetrumpet
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.