change timezone in phpmyadmin current_timestamp

[shrikant rao]

Hi all,

I m using a server situated at US. and one of my table contain
'current_timestamp' so its picking the time different from IST. So my
questing here is can I do some setting on phpmyadmin so that it
automatically change the time to IST...


Thank you.

[ykorotia]

don't know about your situation, but timestamp is always in UTC

if you want to show time to your users from db you can do it by

select (current_timestamp + interval $hour_diff hour) as
current_timestamp;

where $hour_diff is hour diff of your timezone against UTC,
example, for Ukraine it is 2 hours, for Canada it is -7 hours

[Mike Smith]

Set your desired timezone in the host accounts local php.ini file.

example: date.timezone = "Europe/London" 

http://www.php.net/manual/en/timezones.php

Mike

--
You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.
To post to this group, send an email to joomla-de...@googlegroups.com.
To unsubscribe from this group, send email to joomla-dev-gene...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/joomla-dev-general?hl=en-GB.

[shrikant rao]

Hello

Thanks for the brilliant replies. Ykorotia, can i set the query provided by you on phpmyadmin as the default query. Its like when ever i appends a new record it automatically takes the date in my timezone (that is 9:30 hours + the time i m currently getting). The idea is to save the current time instead of only displaying.

Thank you.

--
You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.
To post to this group, send an email to joomla-de...@googlegroups.com.
To unsubscribe from this group, send email to joomla-dev-gene...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/joomla-dev-general?hl=en-GB.

--
Thanks and Regards

Shrikant Rao
Mo- 9892728882

[Yurii Korotia]

I guess you didn't catch the idea.

mysql> create table a (t timestamp, d datetime);
Query OK, 0 rows affected (0.14 sec)






mysql> insert a (d) values (now());
Query OK, 1 row affected (0.02 sec)

mysql> select * from a;
+---------------------+---------------------+
| t | d |
+---------------------+---------------------+
| 2011-07-18 10:12:41 | 2011-07-18 10:12:41 |
+---------------------+---------------------+
1 row in set (0.00 sec)

now I change timezone and restart sql server (i don't have timezone
table)


mysql> select * from a;

+---------------------+---------------------+
| t | d |
+---------------------+---------------------+
| 2011-07-18 11:12:41 | 2011-07-18 10:12:41 |
+---------------------+---------------------+
1 row in set (3.03 sec)


as you may see, timestamp always returns time according to your
current server's timezone, as timestamp saves data as UTC.


By ther way, Mike,
I have tried to set timezone but it doesn't change my output from
mysql:
date_default_timezone_set('UTC');
as it changes timezone used by datetime() class of php and so on, not
mysql

[Yurii Korotia]

so, you can:
a. install timezone table into mysql and set any timezone per
connection or mysql server;
b. change mysql ini for timezone
c. change system timezone of your server

http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

that's all options I know by now. Know more? say it. thanks

p.s.
your question was not related to joomla, really

[Mike Smith]

I would respectfully suggest using a mechanism that keeps both the code (php) and mysql in sync - ie using the same time object, using different mechanisms to "get/set" time will inevitably lead to inconsistancies and problems.

http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

Mike.