diff options
Diffstat (limited to 'mysql-test/t/time_zone_func.test')
| -rw-r--r-- | mysql-test/t/time_zone_func.test | 188 |
1 files changed, 188 insertions, 0 deletions
diff --git a/mysql-test/t/time_zone_func.test b/mysql-test/t/time_zone_func.test new file mode 100644 index 00000000000..1d9bc954d8a --- /dev/null +++ b/mysql-test/t/time_zone_func.test @@ -0,0 +1,188 @@ +############# mysql-test\t\time_zone_func.test ############################# +# # +# Variable Name: time_zone # +# Scope: GLOBAL, SESSION # +# Access Type: Dynamic # +# Data Type: STRING # +# Default Value: TRUE 1, FALSE 0 # +# Values: FALSE 0 # +# # +# # +# Creation Date: 2008-02-25 # +# Author: Sharique Abdullah # +# # +# Description: Test Cases of Dynamic System Variable "time_zone" # +# that checks behavior of this variable in the following ways # +# * Functionality based on different values # +# # +# Reference: http://dev.mysql.com/doc/refman/5.1/en/set-option.html # +# # +############################################################################ + +--echo ** Setup ** +--echo +# +# Setup +# + +SET @session_time_zone = @@SESSION.time_zone; +SET @global_time_zone = @@GLOBAL.time_zone; + +CREATE TABLE t1 (a datetime); +CREATE TABLE t2 (a timestamp); + +--echo '#-----------------------------FN_DYNVARS_177_01--------------------#' +# +# Value DEFAULT +# +SET @@time_zone = DEFAULT; + +SELECT @@time_zone; +--echo SYSTEM Expected + +--echo '#-----------------------------FN_DYNVARS_177_02-------------------#' +# +# Testing difference values +# + +SET @@time_zone = '+05:00'; + +SELECT @@time_zone; +--echo +05:00 Expected + +SET @@time_zone = '-01:00'; + +SELECT @@time_zone; +--echo -01:00 Expected + +SET @@time_zone = '+00:00'; + +SELECT @@time_zone; +--echo +00:00 Expected + +SET @@time_zone = '-00:00'; + +SELECT @@time_zone; +--echo -00:00 Expected + +--echo '#-----------------------------FN_DYNVARS_177_03----------------#' +# +# Setting possible values +# + +SET @@time_zone = '+00:00'; + +INSERT INTO t1 VALUES('2008-03-05 16:28:00'); +INSERT INTO t1 VALUES('1970-01-01 00:05:00'),('1970-01-01 01:05:00'); +INSERT INTO t2 VALUES(20080305162800); +INSERT INTO t2 VALUES(19730101235900); +INSERT INTO t2 VALUES('1970-01-01 00:05:00'),('1970-01-01 01:05:00'); +INSERT INTO t2 VALUES(19700101000500); + +SELECT a,UNIX_TIMESTAMP(a) FROM t1; +SELECT a,UNIX_TIMESTAMP(a) FROM t2; + +SET @@time_zone = 'MET'; + +SELECT a,UNIX_TIMESTAMP(a) FROM t1; +SELECT a,UNIX_TIMESTAMP(a) FROM t2; + +SET @@time_zone = '+05:00'; + +SELECT a,UNIX_TIMESTAMP(a) FROM t1; +SELECT a,UNIX_TIMESTAMP(a) FROM t2; + +SET @@time_zone = '+06:00'; +SELECT a,UNIX_TIMESTAMP(a) FROM t1; +SELECT a,UNIX_TIMESTAMP(a) FROM t2; + +SET @@time_zone = '+01:00'; + +SELECT a,UNIX_TIMESTAMP(a) FROM t1; +SELECT a,UNIX_TIMESTAMP(a) FROM t2; + +SET @@time_zone = '+02:00'; + +SELECT a,UNIX_TIMESTAMP(a) FROM t1; +SELECT a,UNIX_TIMESTAMP(a) FROM t2; + +SET @@time_zone = '+00:00'; + +SELECT a,UNIX_TIMESTAMP(a) FROM t1; +SELECT a,UNIX_TIMESTAMP(a) FROM t2; + +SET @@time_zone = '+06:00'; + +SELECT a,UNIX_TIMESTAMP(a) FROM t1; +SELECT a,UNIX_TIMESTAMP(a) FROM t2; + +--echo Potential Feature: upon recovery of timezone, +--echo the original value of the timestamp should be recovered. + +--echo '#-----------------------------FN_DYNVARS_177_04--------------------#' +# +# Testing invalid values +# +--error ER_UNKNOWN_TIME_ZONE +SET @@time_zone = '6'; + + +--echo '#-----------------------------FN_DYNVARS_177_05---------------#' +# +# Session data integrity check & GLOBAL Value check +# + +SET GLOBAL time_zone = 'SYSTEM'; + +--echo ** Connecting con_int1 using root ** +connect (con_int1,localhost,root,,); + +--echo ** Connection con_int1 ** +connection con_int1; +SELECT @@SESSION.time_zone; +--echo SYSTEM Expected + +SET SESSION time_zone = '+05:00'; + +--echo ** Connecting con_int2 using root ** +connect (con_int2,localhost,root,,); + +--echo ** Connection con_int2 ** +connection con_int2; +SELECT @@SESSION.time_zone; +--echo SYSTEM Expected + +SET SESSION time_zone = '-10:00'; + +--echo ** Connection con_int2 ** +connection con_int2; +SELECT @@SESSION.time_zone; +--echo -10:00 Expected + +--echo ** Connection con_int1 ** +connection con_int1; +SELECT @@SESSION.time_zone; +--echo +05:00 Expected + + +SELECT @@GLOBAL.time_zone; +--echo SYSTEM Expected + +--echo ** Connection default ** +connection default; + +--echo Disconnecting Connections con_int1, con_int2 +disconnect con_int1; +disconnect con_int2; + + +# +# Cleanup +# + + +SET @@SESSION.time_zone = @session_time_zone; +SET @@GLOBAL.time_zone = @global_time_zone; + +DROP TABLE t1; +DROP TABLE t2; |