[MDEV-10575] sql_mode=ORACLE: Date and timestamp formats - Jira

XML

Word

Printable

When running with sql_mode=ORACLE, MariaDB should emulate Oracle-style behavior related to date and timestamp formats.

Oracle uses '17-AUG-16' and '17-AUG-16 06.25.36.000000 PM' as the default format for the DATE and TIMESTAMP data types:

DROP TABLE t1;

CREATE TABLE t1 (d DATE, ts TIMESTAMP);

INSERT INTO t1 VALUES (CURRENT_DATE, CURRENT_TIMESTAMP);

SELECT * FROM t1;

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

TS

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

17-AUG-16

17-AUG-16 06.27.16.000000 PM

Formats are determined by session parameters:

SELECT * FROM nls_session_parameters

WHERE  parameter LIKE '%FORMAT%';

NLS_DATE_FORMAT          DD-MON-RR

NLS_TIME_FORMAT          HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT     DD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT       HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT  DD-MON-RR HH.MI.SSXFF AM TZR

The above parameters detemine both input and output formats and can be changed using an ALTER SESSION query.

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

DROP TABLE t1;

CREATE TABLE t1 (d DATE);

INSERT INTO t1 VALUES ('2001-10-20');

INSERT INTO t1 VALUES (CURRENT_DATE);

SELECT * FROM t1;

----------

2001-10-20

2016-08-17

is blocked by

MDEV-4912 Data type plugin API version 1

relates to

MDEV-11829 Please add support for datetime with time zone literals (ISO6801)

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.