0

I have a chat program where when a user sends a chat, an automatic time-stamp is created in mySQL. When the chat log is displayed, I need to adjust all of the time-stamps based on the time zone each user is in...

ie.

Someone in California sees:

Bob: Hi (May 30 2019 10:00 AM)
Rob: Bye (May 30 2019 10:01 AM)

Someone in Chicago sees:

Bob: Hi (May 30 2019 12:00 AM)
Rob: Bye (May 30 2019 12:01 AM)

Meaning I can't store the timestamps in the database accounting for timezone, otherwise all users would see something like this:

(Assuming bob is in California and Rob is in Chicago, and the messages were sent 1 minutes apart)

Bob: Hi (May 30 2019 10:00 AM)
Rob: Bye (May 30 2019 12:01 AM)

So when I pull out my date/times, here is the php code I use:

if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
    echo "<label style = 'font-size:1.1vw';>".date('M j Y g:i A', strtotime($row['time']))."</label>"."<br>";
}

} else {

}
$conn->close();

A PHP solution would be most ideal for me, but I am open to implementing javascript or jQuery solutions as well.

(P.S - I have no idea what time zone mySQL is storing, but it is not my current time zone... so a time-offset solution that goes off of my current time zone needs some kind of a workaround).

miken32
  • 42,008
  • 16
  • 111
  • 154
TylerHough
  • 25
  • 1
  • 11

2 Answers2

0

Possible solution here

In JavaScript you would do something like this:

var dateToConvert = new Date(<?= json_encode($row['time']) ?>);
console.log('full date is ', dateToConvert.toLocaleString());
console.log('just the date is ', dateToConvert.toLocaleDateString());
console.log('just the time is ', dateToConvert.toLocaleTimeString());
Ben Hulan
  • 537
  • 2
  • 7
0

Presumably each user has a preferred timezone stored somewhere? Then it's a simple matter of adjusting at the database level using that value. You don't provide any sample code, but assuming you've got a user object with America/Los Angeles stored as their preferred timezone, you could do something like this:

SELECT message_body, CONVERT_TZ(message_time, 'SYSTEM', ?) AS message_time FROM messages;

And pass in the current user's timezone as a parameter from PHP

miken32
  • 42,008
  • 16
  • 111
  • 154
  • Well, generally one uses an SQL query to pull out messages for the client requesting them. What kind of architecture do you have that you do a single database query and return the same results to every user? – miken32 May 30 '19 at 17:41
  • It's like a public chat sort of thing. There is one table that stores user profiles, and another table that stores messages (with columns: user, message, time). When a user sends a chat, their username is logged/stored, their message logged/stored/displayed, and a timestamp is created by mySQL auto timestamp... Then the entire "messages" log is displayed and auto-updated for all too see. I am also working on 1 on 1 chats too, but I was going to figure this out first. check out: http://www.glass.hol.es/ – TylerHough May 30 '19 at 17:48
  • And yeah, I want to display different things for different users, but I can't store them in a DB with their own timezone taken into account (without issues). – TylerHough May 30 '19 at 17:55
  • But that "messages log" is still being requested by users, unless you're writing it to a static file. – miken32 May 30 '19 at 17:59
  • Well it's being done by a jQuery request that is triggered every half a second or so. All that jQuery does is refresh/display the contents of the messages table. So what you're proposing that I do is when I'm pulling out the date/time, I should be converting it immediately. Okay, yeah that will work lol, I think I just misunderstood you. I'll try it out. I just don't know what you mean by "but assuming you've got a user object with America/LA stored..." I'm creating auto-generated timestamps from mySQL directly, i have no clue what timezone it's stored under unfortunately... – TylerHough May 30 '19 at 18:18
  • What I meant was, your users must have a way to say what timezone they want times to be displayed in? As long as it's stored [in a way that MySQL can use](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_convert-tz), you're set. – miken32 May 30 '19 at 18:28