If you have an EC2 instance to run your application and an RDS instance for your database. On your EC2 instance you are in luck as you can easily change the time zone of the machine. Like set it to GST or by location like below
# ln -sf ../usr/share/zoneinfo/Asia/Dubai /etc/localtime
The real issue is that you can’t change your timezone on your MySQL RDS instance its default it UTC and you cannot modify this. Making our timestamp 4 hours behind.
3 Workaround solutions gathered are the following.
1. Set Time Zone per connection
eg. SET time_zone = ‘Asia/Dubai’;
2. MySQL convert_tz function per insert
insert into table (timestamp) values (convert_tz(now(), ‘GST’, ‘Asia/Dubai’));
3. Manually getting timezone by code
$date = date(‘Y-m-d H:i:s’);
insert into table(date) values (‘” . $date .”‘)
Another issue I have is to update old timestamp records in my database to adjust it by 4 hours. If you have a table with old records and want to update the timezone by adding/minus (x) hours to adjust you can try this query.
UPDATE table SET datefield= DATE_ADD(datefield, INTERVAL 4 HOUR);