>Description:
After setting the TZ environment variable to a different timezone, then
using the UNIX_TIMESTAMP(...) function on a mysql timestamp string brings back the
correct time, but a date that is a day out, but only for certain dates. It seems that
the date becomes the original date plus the difference in the timezones. Only the date
is affected - not the time. Ie if the new timezone is +5 hours difference, then it
will add 5 hours to the timestamp, use that date, then use the original time of the
timestamp.
Since a timestamp is a timestamp, it should not be affected by the timezone when
displaying it.
>How-To-Repeat:
1. create a timestamp in the database
2. using a select statement, apply the UNIX_TIMESTAMP function to the timestamp
3. change the TZ environment var to another timezone
4. using a select statement, apply the UNIX_TIMESTAMP function to the timestamp
repeat these steps for each of the 24 hours in the day for the original timestamp, and
you will see that some of them will have different dates.
>Fix:
???
>Submitter-Id: <submitter ID>
>Originator: Dominic Gamble
>Organization:
>MySQL support: [none | licence | email support | extended email support ]
>Synopsis: unix_timestamp function out by a day when using a different timezone
>Severity:
>Priority:
>Category: mysql
>Class:
>Release: mysql-3.23.47 (Source distribution)
>Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.47, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 3.23.47-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 2 hours 27 min 11 sec
Threads: 7 Questions: 1291 Slow queries: 0 Opens: 61 Flush tables: 1 Open tables:
14 Queries per second avg: 0.146
>Environment:
System: Linux kepler 2.4.14 #5 Thu Nov 22 11:29:10 EST 2001 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs
gcc version 2.95.4 20011006 (Debian prerelease)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Mar 20 18:08 /lib/libc.so.6 -> libc-2.2.5.so
-rwxr-xr-x 1 root root 1170812 Feb 4 18:03 /lib/libc-2.2.5.so
-rw-r--r-- 1 root root 2667532 Feb 4 18:03 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Feb 4 18:03 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr --exec-prefix=/usr
--libexecdir=/usr/sbin --datadir=/usr/share --sysconfdir=/etc/mysql
--localstatedir=/var/lib/mysql --includedir=/usr/include --infodir=/usr/share/info
--mandir=/usr/share/man --enable-shared --with-libwrap --enable-assembler
--with-berkeley-db --with-innodb --enable-static --enable-shared --with-raid
--enable-thread-safe-client --without-readline
--with-unix-socket-path=/var/run/mysqld/mysqld.sock --with-mysqld-user=mysql
--without-bench --with-client-ldflags=-lstdc++ --with-extra-charsets=all
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
