CODEWITHSUNDEEP
phpmysqlserver
Bilal Zahid
Bilal Zahid

Reputation: 31

SQL Time Zone Issue

I am working on a project based on PHP i have an issue that i purchased a hosting whose server is of another country and i am in Pakistan when i enter data in database table from PHPMyAdmin in enters the date of that country which is 11 hours behind us that's why my insert queries and update queries not working Php time zone is set but server time zone is not set.

php_value date.timezone 'Asia/Karachi';

i use this is my htaccess file Also use 

    date.timezone = "Asia/Karachi"

in php ini file

Upvotes: 0

Views: 621

Answers (2)

Wiimm
Wiimm

Reputation: 3570

(To long for comment)

If you write a website with user specific timezones, then managing timezones at database site is a bit complicated. I prefer this solution:

  • Try to store all timestamp as bigint values in unix time.
  • Converting time to string only at user interface.

Examples

For Database storing I use:

UPDATE table SET start_time = UNIX_TIMESTAMP();

Results are either retrieved as native integers or converted to UTC/GMT time:

SELECT unix_time, UNIX_TIMESTAMP(db_date)

You can test it with:

SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP(now());

In PHP, you can now simply do fast time calculations by adding and substracting. It printing a time at the user interface, set the timezone (maybe evaluated by a database query) and use date() or strftime(), or any date+time class.

Upvotes: 1

night-gold
night-gold

Reputation: 2441

You should set your MySQL timezone when you open the connexion to the database server in PHP.

You can see some example here: Set timezone in PHP and MySQL

You could also do it like this (works with MariaDB, never tested on MySQL):

$db->exec("SET time_zone='Asia/Karachi';");

That way you don't have to update your database configuration and you can update the set depending on a variable.

Upvotes: 0

Related Questions