time() capturing wrong time

[ajetrumpet]

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.

[Barand]

How is the time stored in your table?

[requinix]

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.

[ajetrumpet]

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.

[ajetrumpet]

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.

[requinix]

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?

 

[ajetrumpet]

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.

[ajetrumpet]

totally unrelated to this thread, but pretty funny stuff.  I had to share:

https://drive.google.com/open?id=1T1CyyWkCcYOzr-pTuiZXP2nI98ZwwFOe

any church goers here?

Edited October 19, 2019 by ajetrumpet

[requinix]

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?

[Barand]

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?

[ajetrumpet]

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 October 19, 2019 by ajetrumpet

[Barand]

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?

[ajetrumpet]

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.

[Barand]

At last! An answer.

[ajetrumpet]

10 minutes ago, Barand said:

At last! An answer.

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

[Barand]

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

[ajetrumpet]

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..

[Barand]

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.

[Barand]

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 October 19, 2019 by Barand

[ajetrumpet]

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.

 

[Barand]

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 October 19, 2019 by Barand

[ajetrumpet]

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 October 19, 2019 by ajetrumpet

[Barand]

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

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

 

[ajetrumpet]

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?

 

[Barand]

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