การแบ่งวันที่และเวลาของ SQL/Postgres/การทำให้เป็นมาตรฐาน

ฉันมีตารางกิจกรรมนี้

+--------------+------------------+
| Field        | Type             |
+--------------+------------------+
| id           | int(11) unsigned |
| start_date   | timestamp        |
| end_date     | timestamp        |
| ...          |                  |
+--------------+------------------+

ฉันต้องการมุมมองว่ากิจกรรมเหล่านี้จัดกลุ่มตาม start_date ภายใน DAY แต่ในลักษณะที่หาก end_date ไม่ได้อยู่ในวันเดียวกันกับ start_date มุมมองจะมีรายการอีกครั้ง แต่โดยที่ start_date ตั้งค่าเป็น 00:00 ของวันถัดไป วัน.. (และอื่นๆ ทำซ้ำได้หลายครั้งตามต้องการ จนกระทั่ง start_date อยู่ในวันเดียวกับ end_date)

ตัวอย่างเช่น:

หากตารางกิจกรรมประกอบด้วย:

+--------------+----------------------------+----------------------------+
| id           | start_date                 | end_date                   |
+--------------+----------------------------+----------------------------+
|  1           | 2014-12-02 14:12:00+00     | 2014-12-03 06:45:00+00     |   
|  2           | 2014-12-05 15:25:00+00     | 2014-12-05 07:29:00+00     |                                
+--------------+----------------------------+----------------------------+

มุมมองควรมี:

+--------------+----------------------------+----------------------------+
| activity_id  | start_date                 | end_date                   |
+--------------+----------------------------+----------------------------+
|  1           | 2014-12-02 14:12:00+00     | 2014-12-02 23:59:59+00     |   
|  1           | 2014-12-03 00:00:00+00     | 2014-12-03 06:45:00+00     |
|  2           | 2014-12-05 15:25:00+00     | 2014-12-05 07:29:00+00     |                                  
+--------------+----------------------------+----------------------------+

ความช่วยเหลือใด ๆ ที่จะได้รับการชื่นชมอย่างมาก!

PS: ฉันใช้ postgresql


postgresql sql database datetime views
person Alexandr    schedule 10.12.2014    source แหล่งที่มา

คำตอบ (1)


arrow_upward
1
arrow_downward

หากต้องการรับแถวที่จำเป็น ให้เริ่มโดยใช้ตั้งค่าฟังก์ชันส่งคืน พร้อมด้วยการรวมด้านข้าง จากนั้น ให้ใช้คำสั่ง CASE และเลขคณิตวันที่ เพื่อดึง ค่าที่เกี่ยวข้อง

นี่คือตัวอย่างในการเริ่มต้น:

with data as (
  select id, start_date, end_date
  from (values
    (1, '2014-12-02 14:12:00+00'::timestamptz, '2014-12-03 06:45:00+00'::timestamptz),
    (2, '2014-12-05 15:25:00+00'::timestamptz, '2014-12-05 07:29:00+00'::timestamptz)
  ) as rows (id, start_date, end_date)
)
select data.id,
      case days.d = date_trunc('day', data.start_date)
        when true then data.start_date
        else days.d
      end as start_date,
      case days.d = date_trunc('day', data.end_date)
        when true then data.end_date
        else days.d + interval '1 day' - interval '1 sec'
      end as end_date
from data
join generate_series(
      date_trunc('day', data.start_date),
      date_trunc('day', data.end_date),
      '1 day'
      ) as days (d)
      on days.d >= date_trunc('day', data.start_date)
      and days.d <= date_trunc('day', data.end_date)

 id |       start_date       |        end_date        
----+------------------------+------------------------
  1 | 2014-12-02 15:12:00+01 | 2014-12-02 23:59:59+01
  1 | 2014-12-03 00:00:00+01 | 2014-12-03 07:45:00+01
  2 | 2014-12-05 16:25:00+01 | 2014-12-05 08:29:00+01
(3 rows)

นอกจากนี้ ขึ้นอยู่กับสิ่งที่คุณกำลังทำ อาจเหมาะสมกว่าสำหรับคุณที่จะใช้ ช่วงวันที่:

with data as (
  select id, start_date, end_date
  from (values
    (1, '2014-12-02 14:12:00+00'::timestamptz, '2014-12-03 06:45:00+00'::timestamptz),
    (2, '2014-12-05 07:25:00+00'::timestamptz, '2014-12-05 15:29:00+00'::timestamptz)
  ) as rows (id, start_date, end_date)
)
select data.id,
      tstzrange(data.start_date, data.end_date)
from data;

 id |                      tstzrange                      
----+-----------------------------------------------------
  1 | ["2014-12-02 15:12:00+01","2014-12-03 07:45:00+01")
  2 | ["2014-12-05 08:25:00+01","2014-12-05 16:29:00+01")
(2 rows)
person Denis de Bernardy    schedule 10.12.2014