The time zone matters... your results are exactly 8 hours off... PST is gmt -8. So it looks like the from_unixtime function is converting to what the time was locally at that moment in GMT. Not what I would have expected either....
What do you get when you run- select unix_timestamp(urtime) from t_test; > -----Original Message----- > From: Ron McKeever [mailto:[EMAIL PROTECTED] > Sent: Tuesday, December 02, 2003 10:58 AM > To: [EMAIL PROTECTED] > Subject: unixtime update syntax > > > 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
