แก้ปัญหาการเขียน Query ยาวๆ กับการจัดการเรื่องวันที่ใน Hospital OS

สวัสดีค่ะ สืบเนื่องมาจากได้คุยกับคุณลุงคนหนึ่งแกเป็น เภสัชกรอยู่ที่โรงพยาบาลท่าแพ จังหวัดสตูล และเราก็คุยกันหลายเรื่องค่ะ คือเราพบว่าเรามีเป้าหมายเดียวกัน คือ อยากให้สิ่งดีๆ เกิดขึ้นในชุมชนแห่งการเรียนรู้ การแบ่งปันความรู้ และอยากให้คงอยู่ตลอดไป อันนี้ไม่ได้เกี่ยวกับอะไรทั้งนั้น เป็นเพียงกลุ่มคน กลุ่มหนึ่งที่ต้องการสร้างสรรสิ่งดีๆ และแบ่งปันกัน ก็เท่านั้นเอง

แต่สัจจธรรมก็คือ ต่างคนก็ต่างมีภาระ หน้าที่ ความรับผิดชอบ ... ใช่เราไม่ได้หมายถึง 24 ชั่วโมงของเราจะมาทุ่มเทกับสิ่งหนึ่งเพราะมันเป็นไปไม่ได้ แต่เราหาเวลาเพื่อมาทุ่มเทต่างหาก เรามีงานมีหน้าที่ เรามีครอบครัว เรามีสังคม เรามีชุมชน เราสามารถมีความสุขและทำได้ถ้าเรารู้จักความพอดี ก็บอกแล้วความสุขของคนต่างกัน ... เลือกเอาว่าจะมีความสุขแบบไหน ลองถามตัวเองว่าเคยได้รับผลตอบแทนจากการกระทำที่เป็นรอยยิ้มบ้างมั้ย ได้มองสิ่งที่เราทำแล้วอมยิ้มอยู่คนเดียว จะมีความสุขมากแค่ไหน ... ลองทำดู

เกือบจะไปบวชชีแล้ว ... แต่ก็ยังละทางโลกไม่ได้ที อิอิ
งั้นเรามาเข้าเรื่องกันดีกว่าค่ะ  เพื่อให้เราทำงานง่ายขึ้นเราก็เขียนตัวช่วยมาซักตัวเอาไว้เรียกใช้กันนะคะ
สำหรับที่นำมาฝากกันวันนี้ เป็นเรื่องการหาผลต่างของเวลา ซึ่งโดยปกติ Hospital OS เก็บข้อมูลวันที่เป็น varchar ในลักษณะของปี พ.ศ. เช่น '2553-09-04,10:16:38'  จะเก็บข้อมูลไว้แบบนี้ค่ะเวลาเรานำข้อมูลมาใช้งาน เราก็ต้องเข้าใจว่าข้อมูลเก็บอยู่อย่างไร

มาเล่าเรื่องวันที่กันอีกทีนะคะ ขึ้นอยู่กับว่าเรานำข้อมูลไปใช้ยังไง
สมมติข้อมูล
t_visit.visit_financial_discharge_time = '2553-04-03,01:22:24'
t_visit.visit_begin_visit_time = '2553-03-30,13:46:48'

1. เรานำไปแสดงในรายงานโดยตรง ส่วนใหญ่ผู้ใช้งานต้องการ เป็น พ.ศ. อยู่แล้ว
เราสามารถ Substring ไปได้เลยค่ะ
ใช้คำสัง -> substring(t_visit.visit_financial_discharge_time,1,10)
ผลลัพธ์ -> 2553-04-03
2. ออกแฟ้มรายงาน หรือ ส่งเข้าโปรแกรมอื่นที่ต้องการข้อมูลรูปแบบ ค.ศ.
ใช้คำสัง -> cast(substring(t_visit.visit_financial_discharge_time,1,4) as numeric) - 543 || substring(t_visit.visit_financial_discharge_time,5,6)
ผลลัพธ์ -> 2010-04-03
3. ต้องการคำนวนวันจากวันที่ การใช้ Function เกี่ยวกับวันที่นั้นโดยปกติของฐานข้อมูลจะมองข้อมูลวันที่เป็น ค.ศ. ดังนั้นหากเราต้องการได้ข้อมูลที่ถูกต้องจริงๆ เราจะต้องแปลงข้อมูลให้เป็น ค.ศ. เสียก่อน (อ้างอิงจาก ข้อ 2 ) หลังจากนั้นเราก็ค่อยนำมาใช้กับ Function อีกที ในที่นี้จะยกตัวอย่างการแปลงวันที่ เพื่อลบกันได้ นั่นคือ
3.1 เราทำการแปลวันที่สิ้นสุดก่อน ในที่นี้ขอใช้ วันที่จำหน่ายทางการเงิน
cast(substring(t_visit.visit_financial_discharge_time,1,4) as numeric) - 543 || substring(t_visit.visit_financial_discharge_time,5,6)
จะได้วันที่ในรูปแบบ ค.ศ.
หลังจากนั้นก็ cast ให้เป็นวันที่ เพื่อจะนำไปลบ โดยยกข้างต้นมาทั้งชุดเลยนะคะ
เขียน cast( as date) วางไว้ก่อนก็ได้ จากนั้น copy มาทั้งชุดมาวางหลัง วงเล็บเปิด (
จะได้
cast(cast(substring(t_visit.visit_financial_discharge_time,1,4) as numeric) - 543 || substring(t_visit.visit_financial_discharge_time,5,6) as date)
จากข้อมูลสมมติ จะได้ 3/4/2553
3.2 เราทำการแปลงวันที่เริ่มต้น ในที่นี้ขอใช้วันที่ visit
cast(substring(t_visit.visit_begin_visit_time,1,4) as numeric) - 543 || substring(t_visit.visit_begin_visit_time,5,6)
จะได้วันที่ในรูปแบบ ค.ศ.
หลังจากนั้นก็ cast ให้เป็นวันที่ เพื่อจะนำไปลบ โดยยกข้างต้นมาทั้งชุดเลยนะคะ
เขียน cast( as date) วางไว้ก่อนก็ได้ จากนั้น copy มาทั้งชุดมาวางหลัง วงเล็บเปิด (
จะได้
cast(cast(substring(t_visit.visit_begin_visit_time,1,4) as numeric) - 543 || substring(t_visit.visit_begin_visit_time,5,6) as date)
จากข้อมูลสมมติ จะได้ 30/3/2553
3.3 นำข้อ 3.1 ลบด้วย 3.2
cast(cast(substring(t_visit.visit_financial_discharge_time,1,4) as numeric) - 543 || substring(t_visit.visit_financial_discharge_time,5,6) as date)
-
cast(cast(substring(t_visit.visit_begin_visit_time,1,4) as numeric) - 543 || substring(t_visit.visit_begin_visit_time,5,6) as date)

จากข้อมูลสมมติ จะได้ 4 หน่วยเป็นวัน (วันที่ - วันที่ = จำนวนวัน) 

ทีนี้เราทำแค่นี้อาจจะดูไม่ยุ่งยากนะคะ แต่ถ้าเรานำไปเขียนร่วมกับ Query ยาวแล้วอ่านจะตาลายมั้ยก็ไม่รู้ ...
ลองนำ Function นี้ไปใช้กันมั้ยคะ
function difference_datead_secs เป็น Function สำหรับหาผลต่างของวันที่และเวลา ได้ค่า วินาที ออกมาค่ะ

เริ่มต้นจากนำคำสั่งนี้ไปรัน นะคะ (รันครั้งเดียวก็พอ)
CREATE OR REPLACE FUNCTION difference_datead_secs (varchar, varchar) RETURNS int4 AS
'
declare
    date_start varchar;
    date_end varchar;
    result_secs int;
begin
    date_start = $1;
    date_end = $2;
   
    if (length(date_start) >= 10 and length(date_end) >= 10)
    then  
               result_secs = (date_part(''day'', (cast((cast(substring(date_end,1,4) as numeric) - 543) || substring(date_end,5) as timestamp)
                                                 -
                                               cast((cast(substring(date_start,1,4) as numeric) - 543) || substring(date_start,5) as timestamp))) * 60 * 60 * 24
                                    +
                                    date_part(''hour'', (cast((cast(substring(date_end,1,4) as numeric) - 543) || substring(date_end,5) as timestamp)
                                                 -
                                               cast((cast(substring(date_start,1,4) as numeric) - 543) || substring(date_start,5) as timestamp))) * 60 * 60
                                    +
                                    date_part(''min'', (cast((cast(substring(date_end,1,4) as numeric) - 543) || substring(date_end,5) as timestamp)
                                                 -
                                               cast((cast(substring(date_start,1,4) as numeric) - 543) || substring(date_start,5) as timestamp))) * 60
                                    +
                                    date_part(''sec'', (cast((cast(substring(date_end,1,4) as numeric) - 543) || substring(date_end,5) as timestamp)
                                                 -
                                               cast((cast(substring(date_start,1,4) as numeric) - 543) || substring(date_start,5) as timestamp))) ) ;

    else    result_secs = 0;
        
    end if;

    return result_secs;
end;
'
LANGUAGE 'plpgsql';

หลังจากนั้นเวลาเรียกใช้ 
select
t_visit.t_patient_id
,t_visit.t_visit_id
,t_visit.visit_vn
,t_visit.visit_begin_visit_time
,t_visit.visit_financial_discharge_time
,t_visit_service.b_service_point_id
,t_visit_service.assign_date_time
,t_visit_service.visit_service_treatment_date_time
,t_visit_service.visit_service_finish_date_time
-- บรรทัดข้างล่างเป็นการเรียกใช้ โดยส่งวันที่เข้าไปไม่ต้องแปลงข้อมูลใน Function ที่เราสร้างไปแล้วนั้นจะแปลงให้เองค่ะ
,difference_datead_secs(t_visit.visit_begin_visit_time,t_visit.visit_financial_discharge_time)
from t_visit inner join  t_visit_service
on t_visit.t_visit_id = t_visit_service.t_visit_id
where t_visit_service.visit_service_finish_date_time <> ''
and t_visit.f_visit_status_id <> '4'
and t_visit.f_visit_type_id = '0'
and substring(t_visit.visit_financial_discharge_time,1,10) = '2553-04-03'
order by
t_visit.t_patient_id
,t_visit.t_visit_id;

ลองดูนะคะ หากไม่เข้าใจก็ถามได้ค่ะ