Anyone have a response to the following:
Hi
I have a db that gets data dumped into it. One of the columns gets unix
timestamp data "utime".
I what to covert that into a datetime column so I can utlize indexes and
such.
But I still what the unixtime to remain.
I know I can get the data I want with php or mysql to convert it, but I need
both columns for this.
One with the unixtime, and one with it converted.
I believe I have a good way to do this but I'm not sure why it's not
converting the date right ( see at bottom ):
mysql> desc t_test;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| a | int(11) | | PRI | 0 | |
| b | varchar(10) | YES | | NULL | |
| utime | varchar(10) | YES | | NULL | |
| urtime | datetime | YES | | NULL | |
the data being inserted:
insert into t_test (a,b,utime) values ('1','test','1070296560');
insert into t_test (a,b,utime) values ('2','test','1070292960');
Monday, December 1st 2003, 16:36:00 (GMT) = 1070296560
Monday, December 1st 2003, 15:36:00 (GMT) = 1070292960
mysql> select * from t_test;
+---+------+------------+--------+
| a | b | utime | urtime |
+---+------+------------+--------+
| 1 | test | 1070296560 | NULL |
| 2 | test | 1070292960 | NULL |
+---+------+------------+--------+
2 rows in set (0.00 sec)
Syntax I'm using to update the datetime column from the varchar columnis:
mysql> UPDATE t_test SET urtime = FROM_UNIXTIME(utime) WHERE urtime is NULL;
mysql> select * from t_test;
+---+------+------------+---------------------+
| a | b | utime | urtime |
+---+------+------------+---------------------+
| 1 | test | 1070296560 | 2003-12-01 08:36:00 |
| 2 | test | 1070292960 | 2003-12-01 07:36:00 |
+---+------+------------+---------------------+
2 rows in set (0.01 sec)
the urtime I thought should read:
2003-12-01 16:36:00
2003-12-01 15:36:00
Does it matter if im on the PST if I get the data from GMT???
Help....
Ron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
