ajetrumpet Posted October 18, 2019 Share Posted October 18, 2019 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted October 18, 2019 Share Posted October 18, 2019 How is the time stored in your table? Quote Link to comment Share on other sites More sharing options...
requinix Posted October 18, 2019 Share Posted October 18, 2019 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. Quote Link to comment Share on other sites More sharing options...
ajetrumpet Posted October 19, 2019 Author Share Posted October 19, 2019 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. Quote Link to comment Share on other sites More sharing options...
ajetrumpet Posted October 19, 2019 Author Share Posted October 19, 2019 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. Quote Link to comment Share on other sites More sharing options...
requinix Posted October 19, 2019 Share Posted October 19, 2019 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? Quote Link to comment Share on other sites More sharing options...
ajetrumpet Posted October 19, 2019 Author Share Posted October 19, 2019 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. Quote Link to comment Share on other sites More sharing options...
ajetrumpet Posted October 19, 2019 Author Share Posted October 19, 2019 (edited) 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 Quote Link to comment Share on other sites More sharing options...
requinix Posted October 19, 2019 Share Posted October 19, 2019 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? Quote Link to comment Share on other sites More sharing options...
Barand Posted October 19, 2019 Share Posted October 19, 2019 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? Quote Link to comment Share on other sites More sharing options...
ajetrumpet Posted October 19, 2019 Author Share Posted October 19, 2019 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted October 19, 2019 Share Posted October 19, 2019 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? Quote Link to comment Share on other sites More sharing options...
ajetrumpet Posted October 19, 2019 Author Share Posted October 19, 2019 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted October 19, 2019 Share Posted October 19, 2019 At last! An answer. Quote Link to comment Share on other sites More sharing options...
ajetrumpet Posted October 19, 2019 Author Share Posted October 19, 2019 10 minutes ago, Barand said: At last! An answer. ? what about the topic of the thread here? is there a solution? Quote Link to comment Share on other sites More sharing options...
Barand Posted October 19, 2019 Share Posted October 19, 2019 There will be - you could have had one hours ago but it took you 12 hours and 6 posts to answer that simple question. Quote Link to comment Share on other sites More sharing options...
ajetrumpet Posted October 19, 2019 Author Share Posted October 19, 2019 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.. Quote Link to comment Share on other sites More sharing options...
Barand Posted October 19, 2019 Share Posted October 19, 2019 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted October 19, 2019 Share Posted October 19, 2019 (edited) 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 Quote Link to comment Share on other sites More sharing options...
ajetrumpet Posted October 19, 2019 Author Share Posted October 19, 2019 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted October 19, 2019 Share Posted October 19, 2019 (edited) 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 Quote Link to comment Share on other sites More sharing options...
ajetrumpet Posted October 19, 2019 Author Share Posted October 19, 2019 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted October 19, 2019 Share Posted October 19, 2019 This will copy the current date and time values into the timestamp column UPDATE tblTraffic SET logged = CONCAT(date, ' ', time); Quote Link to comment Share on other sites More sharing options...
ajetrumpet Posted October 19, 2019 Author Share Posted October 19, 2019 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? Quote Link to comment Share on other sites More sharing options...
Barand Posted October 19, 2019 Share Posted October 19, 2019 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.