Jump to content
ajetrumpet

time() capturing wrong time

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.

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

looks like I'm dealing with a paranoid business owner.  he changed the password on his hosting acct.  I'll get back to you guys when I can get into phpMyAdmin.

Share this post


Link to post
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.

Share this post


Link to post
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?

 

Share this post


Link to post
Share on other sites

i asked it that way cuz the last time i looked at the report, there were 10 records in there with timestamps that were anywhere from 5-8 hours ahead of the local time i was actually looking at the report.

Share this post


Link to post
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?

Share this post


Link to post
Share on other sites
4 hours ago, ajetrumpet said:

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

… And, for the benefit of those of us who can't see the report, how is that?

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

The only reference I found was

$time = date('h:i:a')

in this thread

Are you still storing your times as "08:15:pm" despite being told not to?

Share this post


Link to post
Share on other sites
21 minutes ago, Barand said:

Are you still storing your times as "08:15:pm" despite being told not to?

nope!  they are being stored and displayed as:  hh:mm:ss.  24 hour format.

Share this post


Link to post
Share on other sites
10 minutes ago, Barand said:

At last! An answer.

?  what about the topic of the thread here?  is there a solution?

Share this post


Link to post
Share on other sites

There will be - you could have had one hours ago but it took you 12 hours and 6 posts to answer that simple question.

Share this post


Link to post
Share on other sites

now you're not being cooperative dude.  why make fun of my lack of expertise?  i thought you donated your time to help people who were less knowledgeable than yourself..

Share this post


Link to post
Share on other sites

I do but we cannot  see what you can see. I am not clairvoyant nor am I standing behind you looking over your shoulder at your screen. I don't know your table structure. I cannot see your data. So sometimes there is a need to ask questions in order to give that help.

Share this post


Link to post
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

Share this post


Link to post
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.

 

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

This will copy the current date and time values into the timestamp column

UPDATE tblTraffic SET logged = CONCAT(date, ' ', time);

 

Share this post


Link to post
Share on other sites
1 hour ago, Barand said:

This will copy the current date and time values into the timestamp column


UPDATE tblTraffic SET logged = CONCAT(date, ' ', time);

why would i want to do that?

 

Share this post


Link to post
Share on other sites

As an easier alternative to

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

Then you will just need

SELECT TIME_FORMAT(logged, '%T') as time

 

Share this post


Link to post
Share on other sites

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.