What are the best practices for storing and retrieving timestamps in MySQL databases to ensure accurate date and time display on a website?

To ensure accurate date and time display on a website when storing and retrieving timestamps in MySQL databases, it is important to use the appropriate data type for timestamps, such as DATETIME or TIMESTAMP. Additionally, it is recommended to store timestamps in UTC format to avoid any timezone discrepancies. When retrieving timestamps, use PHP date functions to format the date and time according to the desired timezone.

// Storing timestamp in UTC format
$timestamp = gmdate('Y-m-d H:i:s');

// Retrieving timestamp and formatting for display in a specific timezone
$timestamp = '2022-01-01 12:00:00'; // Retrieve timestamp from database
$timezone = new DateTimeZone('America/New_York'); // Set desired timezone
$date = new DateTime($timestamp, new DateTimeZone('UTC')); // Create DateTime object with UTC timezone
$date->setTimezone($timezone); // Set timezone to desired timezone
echo $date->format('Y-m-d H:i:s'); // Display formatted timestamp