FROM_UNIXTIME takes a Unix timestamp and returns a DATETIME.
I believe the problem you are having is that mysql expects all DATETIMEs to be in your timezone.
For example,
mysql> select from_unixtime(1); +---------------------+ | from_unixtime(1) | +---------------------+ | 1969-12-31 19:00:01 | +---------------------+ 1 row in set (0.00 sec)
mysql> select unix_timestamp('1970-01-01 00:00:01');
+---------------------------------------+
| unix_timestamp('1970-01-01 00:00:01') |
+---------------------------------------+
| 18001 |
+---------------------------------------+
1 row in set (0.00 sec)mysql> select from_unixtime(18001); +----------------------+ | from_unixtime(18001) | +----------------------+ | 1970-01-01 00:00:01 | +----------------------+ 1 row in set (0.00 sec)
I'm in EST, so I'm 5 hours behind. 5 hours = 18000 seconds, so these make sense when you consider that the timezone is always part of DATETIME.
As I see it, the problem you are having is that you've told mysqld at startup that you are in PST (-8), but you are expecting DATETIMES to be in GMT in your urtime column. You could set your timezone to GMT at startup, but then ALL your DATETIMES will be in GMT. Assuming you don't want that, if you want your urtime column to be in GMT, then you must take the offset (PST=-8) into account when you assign values to the column. Try this:
UPDATE t_test SET urtime = DATE_ADD(FROM_UNIXTIME(utime), INTERVAL 8 HOUR) WHERE urtime is NULL;
Michael
Ron McKeever wrote:
Thats seems like a bug to me.
I would think FROM_UNIXTIME would take a unixtime stamp and covert it to what it is. We know its from GMT
UNIX timestamp = The timestamp is the current time measured in the number of seconds since the Unix Epoch (January 1 1970 00:00:00 GMT).
Ron
-----Original Message----- From: Keith C. Ivey [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 8:55 AM To: [EMAIL PROTECTED] Subject: Re: unixtime update syntax
On 2 Dec 2003 at 7:57, Ron McKeever wrote:
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.
You can use indexes with a Unix time column about as well as you can with DATETIME. What sort of queries are you wanting to do? Having the extra column may be unnecessary.
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 ):
It seems to be converting the date right. FROM_UNIXTIME() does the opposite of UNIX_TIMESTAMP(). It takes an integer representing a Unix time and converts it to a DATETIME in local time (not GMT). It would be nice if there were a FROM_UNIXTIME_TO_GMT() function, but there isn't.
-- Keith C. Ivey <[EMAIL PROTECTED]> Tobacco Documents Online http://tobaccodocuments.org
-- 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]
