0

How can I take away 5 hours from the timestamp shown on my webpage?

I have tried setting this as a global option in Mariadb

SET @@time_zone := '-05:00';

The Time then shows the correct time (-5h) in the db table but not on my page.

For Example:

Time in DB: 2021-03-29 15:53:51 Time on page: 2021-03-29 20:53:51

The php inside my html

    <?php
                    // Include config file
                    include('config.php');  
                    echo "<br>";
                    // Attempt select query execution
                    $sql = "SELECT * FROM posts_tbl1 ORDER BY time DESC";
                    if($result = mysqli_query($link, $sql)){
                        if(mysqli_num_rows($result) > 0){
                             echo . $row['time'] .;
                            // Free result set
                            mysqli_free_result($result);
                        } else{
                            echo "<p class='lead'><em>No records were found.</em></p>";
                        }
                    } else{
                        echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
                    }
                    // Close connection
                    mysqli_close($link);
                    ?>

DB

+-----------+---------------+------+-----+---------------------+----------------+
| Field     | Type          | Null | Key | Default             | Extra          |
+-----------+---------------+------+-----+---------------------+----------------+
| id        | int(7)        | NO   | PRI | NULL                | auto_increment |
| time      | timestamp     | NO   |     | CURRENT_TIMESTAMP   |                |
| message   | varchar(5000) | YES  |     | NULL                |                |
| tags      | varchar(30)   | YES  |     | NULL                |                |
| expire    | timestamp     | NO   |     | 0000-00-00 00:00:00 |                |
| pinned    | varchar(10)   | YES  |     | NULL                |                |
| type      | varchar(100)  | YES  |     | NULL                |                |
| name      | varchar(40)   | YES  |     | NULL                |                |
+-----------+---------------+------+-----+---------------------+----------------+
Luke To
  • 103
  • 6
  • Are you using PDO? Anything else? – Jeto Mar 29 '21 at 21:28
  • mysqi - I have included a trimmed down version of the full php – Luke To Mar 29 '21 at 21:35
  • Don't use a `TIMESTAMP` column, use `DATETIME`. Just do the math either when you get the data from MySQL (using the `CONVERT_TZ()` function) or on the PHP side using the `DateTime` class functions. – miken32 Mar 29 '21 at 22:09
  • And don't use numeric time zones like "-05:00" or you will be wrong by an hour for half the year. Use named zones like "America/Toronto" that take summer time into account. – miken32 Mar 29 '21 at 22:10

0 Answers0