List:General Discussion« Previous MessageNext Message »
From:Dominic Gamble Date:March 28 2002 1:06am
Subject:unix_timesamp function out by a day when using a different timezone
View as plain text  
>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

Thread
unix_timesamp function out by a day when using a different timezoneDominic Gamble28 Mar
  • Re: unix_timesamp function out by a day when using a differenttimezonePaul DuBois28 Mar
    • Re: unix_timesamp function out by a day when using a differenttimezoneDominic Gamble28 Mar