[MCOL-3869] Use the server time_zone system variable for functions that it affects in ColumnStore. Created: 2020-03-04  Updated: 2023-11-21  Resolved: 2023-10-25

Status: Closed
Project: MariaDB ColumnStore
Component/s: PrimProc
Affects Version/s: 1.4.0, 1.5.3
Fix Version/s: Icebox

Type: Bug Priority: Minor
Reporter: Gagan Goel (Inactive) Assignee: Gagan Goel (Inactive)
Resolution: Won't Fix Votes: 0
Labels: None

Issue Links:
Blocks
blocks MCOL-3867 from_unixtime() not giving expected o... Closed
PartOf
includes MCOL-3867 from_unixtime() not giving expected o... Closed
includes MCOL-3868 unix_timestamp() output incorrect whe... Closed
is part of MCOL-3594 Failed tests in the 001 suite in 1.4 Closed
Relates
relates to MCOL-1397 MariaDB session variables should be p... Closed
Epic Link: Time, Timestamp and Timezone
Sprint: 2020-6

 Description   

ColumnStore currently does not use the time_zone system variable in MariaDB server in evaluating functions that are affected by time zone.

Functions and data types that are affected by time zone are listed here: https://mariadb.com/kb/en/time-zones/#time-zone-effects

In addition, from_unixtime() also is dependent on the time zone setting.

The goal of this ticket is to add support for time zone for all affected functions in ColumnStore so that these functions behave consistent to the server.

 Comments   
Comment by David Hall (Inactive) [ 2020-03-04 ]

After fixing this, revert the tests to pre MCOL-3594. That is, remove all the stuff to get a constant to try to make it work.

working_tpch1/qa_fe_cnxFunctions/from_unixtime.sql
working_tpch1/qa_fe_cnxFunctions/unix_timestamp.sql

Comment by David Hall (Inactive) [ 2020-04-15 ]

Some study should be done about coming up with a consistent or perhaps singular way to get various Server settings to the disparate parts of Columnstore. MCOL-1397

Comment by Kirill Perov [ 2023-11-20 ]

set time_zone='+6:00';
SHOW VARIABLES like '%zone%';
------------------------+

Variable_name Value

------------------------+

system_time_zone UTC
time_zone +06:00

------------------------+
2 rows in set (0.001 sec)

create table t1(c1 datetime);
create table t2(c1 datetime) engine=columnstore;
insert t1 (select now());
insert t2 (select now());

select * from t1;
---------------------

c1

---------------------

2023-11-21 05:35:47

---------------------
1 row in set (0.001 sec)

select * from t2;
---------------------

c1

---------------------

2023-11-21 05:36:07

---------------------
1 row in set (0.050 sec)

Both MDB and MCS tables got value with time zone applied.

Generated at Thu Feb 08 02:46:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.