Windows time in MySQL

Question

I have a datetime variable in MySQL. Now it's 15:36 14-09-2021 on my computer, but when I store it to MySQL there is 09:36 2021-09-14. What can I do to display it in a way which takes into account Windows time? Do you know how to do that? Do you have any ideas?

This is my current code:

<?php
session_start();
?>
<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Question</title>
    <link rel="stylesheet" href="style.css">
</head>

<body>
    <div>
        <a href="index.php">
            <img id="logo" src="logo.png" width="100px" height="100px" style="margin-left: 400px;">
        </a>
    </div>
    <?php
    $connection = mysqli_connect("XXX", "XXX", "XXX", "XXX");
    if (isset($_POST['answer'])) {
        $stmt = mysqli_prepare($connection, "INSERT INTO answers(questionId, dateAndTime, answer) VALUES (?,(SELECT now()),?)");
        $stmt->bind_param("ss", $a, $b);
        $a = $_SESSION['id'];
        $b = $_POST['answer'];
        $stmt->execute();
    }
    if (isset($_GET['id'])) {
        $stmt = mysqli_prepare($connection, "SELECT posts.title,posts.body,posts.dateAndTime FROM posts WHERE posts.id=?");
        $stmt->bind_param("s", $id);
        $id = $_GET['id'];
        $_SESSION['id'] = $id;
        $stmt->execute();
        $result = $stmt->get_result();
        while ($row = mysqli_fetch_assoc($result)) {
            echo '<div style="display: flex;"><div>', $row['dateAndTime'], '</div><div style="font-size: 42px;">', $row['title'], "</div></div><div>", $row['body'], "</div>";
        }
    } else {
        $stmt = mysqli_prepare($connection, "SELECT posts.title,posts.body,posts.dateAndTime FROM posts WHERE posts.id=?");
        $stmt->bind_param("s", $id);
        $id = $_SESSION['id'];
        $stmt->execute();
        $result = $stmt->get_result();
        while ($row = mysqli_fetch_assoc($result)) {
            echo '<div style="display: flex;"><div>', $row['dateAndTime'], '</div><div style="font-size: 42px;">', $row['title'], "</div></div><div>", $row['body'], "</div>";
        }
    } {
        $stmt = mysqli_prepare($connection, "SELECT answers.answer, answers.dateAndTime FROM answers WHERE answers.questionId=?");
        $stmt->bind_param("s", $id);
        $stmt->execute();
        $result = $stmt->get_result();
        while ($row = mysqli_fetch_assoc($result)) {
            echo '<div style="border-style: solid; margin: 5px; display: inline-block;"><div style="display:flex;"><div>', $row['dateAndTime'], '</div><div style="margin: 5px;">', $row['answer'], "</div></div></div><br>";
        }
    }
    ?>
    <form action="question.php" method="POST" style="display: flex; flex-direction: column;">
        <textarea name="answer" style="margin-top: 100px; width: 25%; height: 200px;"></textarea>
        <button type="submit" class="btn" style=" width: 5%; text-align: center;">Answer</button>
        <input type='hidden' value='send' name='first'>
    </form>
</body>

</html>

Looks like a timezone issue. What is the timezone for your computer, and what is the timezone set in MySQL? — aynber, Sep 14 '21 at 14:27
https://stackoverflow.com/questions/930900/how-do-i-set-the-time-zone-of-mysql this answer might help you to understand your mysql timezone. It's just the difference in your laptop and mysql timezones. — Vivek Choudhary, Sep 14 '21 at 14:31
Datetime stores using the timezone of server you're connected to — Phaelax z, Sep 14 '21 at 14:40
You can use the function `date_default_timezone_set('your continent / your country / your city');` - [PHP: date_default_timezone_get](https://www.php.net/manual/en/function.date-default-timezone-get.php) - [Time zone](https://www.php.net/manual/en/timezones.php). — Sebastian, Sep 14 '21 at 15:02
Does this answer your question? [Should I use the datetime or timestamp data type in MySQL?](https://stackoverflow.com/questions/409286/should-i-use-the-datetime-or-timestamp-data-type-in-mysql) — kmoser, Sep 14 '21 at 16:16
Please note that there're three time zones involved: your operating system, PHP and MySQL. You can easily change those in PHP and MySQL for current session. — Álvaro González, Sep 14 '21 at 16:21

score 0 · Answer 1 · answered Sep 18 '21 at 16:21

First use the following javascript code:

   <script>
        function createCookie(name, value, days) {
            var expires;
            if (days) {
                var date = new Date();
                date.setTime(date.getTime() + (days * 24 * 60 * 60 * 1000));
                expires = "; expires=" + date.toGMTString();
            } else {
                expires = "";
            }
            document.cookie = escape(name) + "=" + escape(value) + expires + "; path=/";
        }                    
        createCookie("timeZone", Intl.DateTimeFormat().resolvedOptions().timeZone, "10");
    </script>

And than just use the following code in PHP:

$date = new DateTime($row['dateAndTime'], new DateTimeZone(date_default_timezone_get()));
$date->setTimezone(new DateTimeZone($_COOKIE["timeZone"]));
$date->format('Y-m-d H:i:s')

Windows time in MySQL

1 Answers1