You say I should be able to mysqldump a table with TIMESTAMP data from a
database residing in NY, move the dump file to a different time zone, load
it into the mysql db in that other time zone, and have the TIMESTAMP data
still be in US/New York time? I have been banging my head for hours now,
trying adding SET TIME_ZONE='US/Easterm' and various tricks, nothing works.
Here's an example, maybe someone can see where I am going wrong:
interps3> show create table something\G
*************************** 1. row ***************************
Table: something
Create Table: CREATE TABLE `something` (
`someColumn` varchar(32) NOT NULL,
`lastmod` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
interps3> select lastmod from something;
+---------------------+
| lastmod |
+---------------------+
| 2009-06-23 09:47:03 |
| 2009-06-23 09:47:03 |
| 2009-06-23 09:47:03 |
| 2009-06-23 09:47:03 |
+---------------------+
4 rows in set (0.00 sec)
[da...@mintz bin]$ ssh [email protected]
Linux taffy 2.4.32-grsec-opteron-peon-1.1.1 #1 SMP Fri Dec 21 15:45:17 PST
2007 i686 GNU/Linux
__ ______
/ /____ _/ _/ _/_ __
/ __/ _ `/ _/ _/ // /
\__/\_,_/_//_/ \_, /
/___/
Welcome to taffy.dreamhost.com
[taffy]$ mysql --defaults-file=.my.sdny.cnf sdnyinterpreters < dump.sql
[taffy]$ mysql --defaults-file=.my.sdny.cnf sdnyinterpreters;
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 84219 to server version: 5.0.67-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT * FROM something;
+-------------------+---------------------+
| someColumn | lastmod |
+-------------------+---------------------+
| bla bla bla bla | 2009-06-23 06:47:03 |
| say shit fool! | 2009-06-23 06:47:03 |
| bleh snyet splurt | 2009-06-23 06:47:03 |
| foo! | 2009-06-23 06:47:03 |
+-------------------+---------------------+
4 rows in set (0.00 sec)
Three hours too early.
Gratefully,
--
David Mintz
http://davidmintz.org/
The subtle source is clear and bright
The tributary streams flow through the darkness
_______________________________________________
New York PHP Community MySQL SIG
http://lists.nyphp.org/mailman/listinfo/mysql
NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com
Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php
