Sunday, May 4, 2014

เฮียไพฯ กับ Date Format

โพสต์ครั้งแรก: 21 พฤศจิกายน 2011

เฮียไพฯ ของเราเป็นผู้จัดการ IT มานานหลายปี วันหนึ่งขณะที่ผมกำลังเขียนคิวรีอยู่ เฮียก็มายืนข้างหลังผม แล้วก็ขอให้ผมช่วยคิวรีในช่วงของวันที่ที่แกอยากดูให้หน่อย ผมก็จัดให้ทันทีใส่ทูคงทูคา (TO_CHAR) ลงไป เฮียแกเห็นหยั่งงั้นแกก็แสดงภูมิรู้ของแกออกมาอีกเช่นเคย "เฮัย ทำไมมันยุ่งยากหยั่งงี้ ดาต้าเบสระดับนี้มันต้องมีวิธีที่ง่ายกว่านี้แน่ หรือว่าคุณไม่รู้วิธีกันแน่เนี่ย" อ๊ะจึ๋ย.. ไหงเป็นแบบนี้ (ฟะ)
แล้วจริงๆ มันยากจริง ๆ หรือที่จะคิวรีเอาค่าวันที่ออกมาน่ะ เรามาดูกันดีกว่า
เวลาคิวรีคอลัมน์ที่มีชนิดข้อมูลเป็นวันที่เราสามารถคิวรีโดยระบุชื่อคอลัมน์ตรง ๆ ในคิวรีได้เลย ซึ่งรูปแบบของวันที่ (เช่น DD/MM/YY หรือ DD-MON-YY) จะเป็นรูปแบบดีฟอลต์ที่กำหนดไว้ อาจจะโดยระบบหรือเราได้กำหนดค่าดีฟอลต์ก็ได้ ก่อนอื่นผมขอแนะนำให้รู้จักกับคอลัมน์ SYSDATE ซึ่งเราสามารถใช้ในลักษณะเป็น PSEUDO Column ซึ่งจริง ๆ แล้วไม่ได้เป็นคอลัมน์ของตารางใด ๆ จริง ๆ, เวลาจะเอาค่ามาดูจะต้องจัดให้อยู่ในรูปของ SELECT + (Pseudo) Column Name + FROM + Table Name เช่น
sys@PAO1> select sysdate from dual;

SYSDATE
---------
10-DEC-11
มีสิ่งที่น่าสนใจจากผลของคิวรีข้างบนคือ 1) รูปของคิวรีเป็นไปตามที่บอกข้างต้นโดยมี SYSDATE เป็น Pseudo Column และ DUAL เป็น Pseudo Table ซึ่ง ณ ที่นี้มีชื่อตารางนี้ไว้เพียงเพื่อให้รูปของคิวรีถูก Syntax จะได้ไม่ Error เท่านั้น ไม่ได้มีความหมายอะไรมากกว่านี้
SYSDATE มีชนิดข้อมูลเป็น Date เวลาเรา Select ตรง ๆ ออกมาโดยไม่ใส่ฟังก์ชันใด ก็จะแสดงผลเป็นฟอร์แมตที่เป็นดีฟอลต์คือ DD-MON-YY
ปกติแม้ว่าจะแสดงผลเฉพาะวันที่ แต่จริง ๆ แล้วชนิดข้อมูลที่เป็น DATE มีการเก็บเอาเวลาไว้ด้วย แต่เวลาแสดงผลหากไม่ได้ระบุก็จะไม่เห็นส่วนที่เป็นเวลา ถ้าเราต้องการให้แสดงผลเป็นเวลาก็จะยุ่งยากขึ้นอีกนิสสนึง
sys@PAO1> select to_char(sysdate,'DD-MON-YY HH24:MI:SSSSS') from dual;

TO_CHAR(SYSDATE,'DD-M
---------------------
10-DEC-11 13:01:46896

sys@PAO1> select to_char(sysdate,'dd-Mon-yy hh:mi AM') from dual

TO_CHAR(SYSDATE,'D
------------------
10-Dec-11 01:05 PM
เอาเป็นว่าถ้าเราต้องการให้แสดงผลเป็นวันที่และเวลาด้วยก็ให้ใช้ฟังก์ชั่น TO_CHAR จัดฟอร์แมทตามที่คุณต้องการ ส่วนที่อยู่ใน Single Quote สังเกตว่าเราสามารถใส่หน่วยของวินาทีได้ละเอียดทีเดียว แต่ในวันปกติเราคงไม่ได้ใช้กันละเอียดขนาดนั้น
เราสามารถให้แสดงผลเป็นเวลาแบบ 24 ชั่วโมง (HH24) หรือเป็นแบบ 12 ชั่วโมง (HH แล้วตามด้วย AM หรือ PM) สังเกตตัวเล็กตัวใหญ๋ด้วยนะครับว่ามีผลต่อฟอร์แมทยังไง ถ้าเราต้องการวันจันทร์,อังคาร,พุธก็ได้แบบนี้
sys@PAO1> select to_char(sysdate,'Day dd-mon-yy hh:mi AM') from dual

TO_CHAR(SYSDATE,'DAYDD-MON-Y
----------------------------
Saturday 10-dec-11 01:13 PM
ถ้าเราอยากรู้ว่าฟอร์แมทดีฟอลต์เป็นยังไง ก็ใช้คิวรีข้างล่างนี้
sys@PAO1> select parameter,value from nls_session_parameters
2 where parameter = 'NLS_DATE_FORMAT';

PARAMETER  VALUE
------------------ ------------------------------
NLS_DATE_FORMAT    DD-MON-RR
RR เป็นฟอร์แมตของการแสดงผลปีคล้าย ๆ กับ YY จะต่างกันตรงที่ถ้าเราใช้ TO_DATE กับ YY เราจะได้ผลที่สองหลักแรกของปีเป็นสองหลักแรกของปีปัจจุบันเท่านั้น แต่ถ้าเราใช้ RR, Oracle จะเลือกสองหลักแรกของปีให้ โดยมีเงื่อนไขดังนี้
- ถ้าปี RR ที่เราระบุเป็น 00 ถึง 49
- ถ้าสองหลักหลังของปีจริง (ปีปัจจุบัน)อยู่ระหว่าง 00 ถึง 49 สองหลักแรกที่ได้จากการใช้ RR ก็จะเป็นเหมือนปีปัจจุบัน
- ถ้าสองหลักหลังของปีจริง (ปีปัจจุบัน)อยู่ระหว่าง 50 ถึง 99 สองหลักแรกที่ได้จากการใช้ RR ก็จะเป็นเหมือนปีปัจจุบัน บวกด้วย 1
- ถ้าปี RR ที่เราระบุเป็น 50 ถึง 99
- ถ้าสองหลักหลังของปีจริง (ปีปัจจุบัน)อยู่ระหว่าง 00 ถึง 49 สองหลักแรกที่ได้จากการใช้ RR ก็จะเป็นเหมือนปีปัจจุบัน ลบด้วย 1
- ถ้าสองหลักหลังของปีจริง (ปีปัจจุบัน)อยู่ระหว่าง 50 ถึง 99 สองหลักแรกที่ได้จากการใช้ RR ก็จะเป็นเหมือนปีปัจจุบัน
ตัวอย่างเช่น ถ้าใช้คิวรีข้างล่างระหว่างปี 1950 และ 1999:

SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR') ,'YYYY') "Year" FROM DUAL;

Year
----
1998

SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR') ,'YYYY') "Year"    FROM DUAL;

Year
----
2017

คิวรีเดียวกันแต่ใช้ในปี ระหว่างปี 2000 and 2049:

SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR') ,'YYYY') "Year"    FROM DUAL;

Year
----
1998

SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR') ,'YYYY') "Year"    FROM DUAL;

Year
----
2017
เราสามารถเปลี่ยนฟอร์แมทที่เป็นดีฟอลต์ได้โดย ALTER SESSION แบบนี้
sys@PAO1> alter session set nls_date_format = 'Day DD Mon. YY HH:MI AM';
Session altered.

sys@PAO1> select parameter,value from nls_session_parameters
2 where parameter = 'NLS_DATE_FORMAT';

PARAMETER VALUE
------------------------------ ------------------------------
NLS_DATE_FORMAT Day DD Mon. YY HH:MI AM

sys@PAO1> select sysdate from dual;

SYSDATE
-----------------------------
Saturday 10 Dec. 11 01:21 PM
แต่ถ้าเราใช้ ALTER SESSION มันจะมีผลต่อค่าดีฟอลต์เฉพาะเมื่อยังไม่ได้ Logout ออกมาเท่านั้น ถ้าเรา Login เข้าไปใหม่ หรือว่าล็อกอินอยู่ใน Session อื่น ก็จะไม่ได้ใช้ดีฟอลต์ที่เรา ALTER SESSION นี้
sys@PAO1> disconn
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

sys@PAO1> conn sys@pao1 as sysdba
Enter password:
Connected.

sys@PAO1> select sysdate from dual;

SYSDATE
---------
10-DEC-11
ถ้าเราต้องการให้แสดงผลเป็นฟอร์แมทที่เราสร้างขึ้นนี้ไปตลอด ไม่ว่าจะล็อกอิน-เอาท์กันกี่ครั้ง หรือจะล็อกอินจาก Session ใดก็ตาม ในการนี้เราจะต้อง ALTER ในระดับที่ใหญ่กว่า SESSION นั่นคือ ALTER SYSTEM ซึ่งเราจะต้องใช้ scope=spfile เพื่อให้ไปบันทึกไว้ใน SPFILE รอการ Restart ครั้งต่อไป ก็จะได้ฟอร์แมทที่ต้องการ หรือจะใช้วิธีการไปแก้หรือเพิ่มในไฟล์ INIT.ORA ก็ได้ ซึ่งจะต้องทำการ Restart เช่นกัน
sys@PAO1> alter system set nls_date_format='Day DD/MM/YY hh24:mi' scope=spfile;
System altered.
sys@PAO1> select sysdate from dual;

SYSDATE
---------
10-DEC-11
sys@PAO1> startup force;
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 1279336 bytes
Variable Size 125831832 bytes
Database Buffers 75497472 bytes
Redo Buffers 7106560 bytes
Database mounted.
Database opened.

sys@PAO1> select sysdate from dual;

SYSDATE
------------------------
Saturday 10/12/11 13:36

sys@PAO1> select parameter,value from nls_session_parameters
2 where parameter='NLS_DATE_FORMAT';

PARAMETER VALUE
------------------------------ ------------------------------
NLS_DATE_FORMAT Day DD/MM/YY hh24:mi

วันนี้พอแค่นี้ก่อนนะครับ เราอาจจะแก้ฟอร์แมทกลับเป็น DD-MON-RR นะครับ โดยส่วนตัวผมคิดว่าฟอร์แมทนี้ดูง่ายดีแม้ว่าจะไม่เห็นรายละเอียดอะไร แต่เราก็สามารถใส่ฟังก์ชั่น TO_CHAR เพื่อให้อยู่ในรูปแบบที่เราต้องการได้ครับ

No comments:

Post a Comment