PostgreSQL: bitiş tarihi için yıl hesaplayın

1 Cevap php

Background

Kullanıcılar olarak, aşağıdaki ekran resminde gösterildiği tarih seçebilirsiniz:

Herhangi ay / gün başlangıç ​​ve bitiş ay / gün kombinasyonları gibi geçerlidir:

  • 22 Mart-22 Haziran
  • Dec 1 ile Feb 28

Ikinci kombinasyon (ben "zor date senaryo" diyoruz) zordur, çünkü starting month/day için ending month/day gelmelidir after yıl yıl. (Ayrıca yukarıda ekran görüntüsü seçili gösterilen) yıl 1900, demek ki, tam tarih olacaktır:

  • Ara 22, 1900 28 Şubat için, 1901
  • Ara 22, 1901 28 Şubat için, 1902
  • ...
  • Ara 22, 2007 28 Şubat için, 2008
  • Ara 22, 2008 28 Şubat için, 2009

Problem

Ne olursa olsun başlangıç ​​ve bitiş günleri seçilir nasıl, başlangıç ​​ay / gün ve bitiş ay / gün arasında kalan tarihlerini içeren bir tablo değerleri seçer bir SQL deyimi yazma. Diğer bir deyişle, bu yıl sarma sorundur.

Inputs

Sorgu parametreleri olarak alır:

  • YEAR1, YEAR2: ay / gün kombinasyonunun bağımsız yıldır dizi.
  • Month1, Gün1: veri toplamak için yıl içinde başlangıç ​​günü.
  • Month2, Day2: yıl içinde biten günü (ya da gelecek yıl) veri toplamak.

Previous Attempt

(O) çalıştı aşağıdaki MySQL kodu göz önünde bulundurun:

end_year = start_year +
  greatest( -1 *
    sign(
      datediff(
        date(
          concat_ws('-', year, end_month, end_day )
        ),
        date(
          concat_ws('-', year, start_month, start_day )
        )
      )
    ), 0
  )

Bu zor tarihi senaryoya göre, nasıl çalışır:

  1. Cari yıl içinde iki tarihleri ​​oluşturun.
  2. Ilk tarih Dec 22, 1900 ve ikinci tarih Feb 28, 1900.
  3. İki tarih arasındaki gün farkı, Kont.
  4. If the result is negative, it means the year for the second date must be incremented by 1. In this case:
    • Cari yıl için 1 ekleyin.
    • Yeni bir bitiş tarihini oluşturun: Feb 28, 1901.
    • Veriler için tarih aralığı başlangıç ​​ve hesaplanan bitiş tarihi arasındaki düşerse görmek için kontrol edin.
  5. Sonuç olumlu ise, tarihler kronolojik ve yapılması gereken hiçbir özel ihtiyaçları temin edilmiştir.

Tarihleri ​​fark. In PostgreSQL, the equivalent functionality always returns a positive number, regardless of their relative chronological order. (My tests of subtracting dates may have been incorrect, though.) pozitif veya negatif olacağını çünkü bu MySQL çalıştı

Question

PostgreSQL dikkate (bu zor yıllık zamansal deplasman tanıtan göre) başlangıç ​​ve ay / gün çiftleri biten kronolojik sipariş almak için nasıl aşağıdaki (kırık) kodu yeniden yazmak gerekir?

SELECT
  m.amount
FROM
  measurement m
WHERE
  (extract(MONTH FROM m.taken) >= month1 AND
  extract(DAY FROM m.taken) >= day1) AND
  (extract(MONTH FROM m.taken) <= month2 AND
  extract(DAY FROM m.taken) <= day2)

Herhangi düşünceler, yorumlar, veya sorular?

(Tarihleri ​​Benim tercihim saf PostgreSQL çözüm içindir. PHP AA / GG biçiminde içine önceden ayrıştırılır, ama ben PHP kullanarak sorunun basit hale olabilir ne önerilerine açığım. SQL çağrılan bir saklı yordam yaşıyor JasperReports tarafından, PHP dokunabilir, sadece ürün raporlama motoru geçti tarihleri ​​böylece.)

Update #1

Aşağıdaki kod neredeyse çalışır:

select
  (extract(YEAR FROM m.taken)||'-12-12')::date as start_date,
  ((extract(YEAR FROM m.taken)+
      greatest(
        0,
        sign(
          (extract(YEAR FROM m.taken)||'-12-12')::date -
          (extract(YEAR FROM m.taken)||'-02-01')::date) ))||'-02-01')::date as end_date,
  m.taken,
  extract(YEAR FROM m.taken) as year_taken
from
  measurement m
where
  m.station_id = 200 AND
  m.category_id = 1 AND
  (m.taken, m.taken) OVERLAPS
    ((extract(YEAR FROM m.taken)||'-12-12')::date,
    ((extract(YEAR FROM m.taken)+
      greatest(
        0,
        sign(
          (extract(YEAR FROM m.taken)||'-12-12')::date -
          (extract(YEAR FROM m.taken)||'-02-01')::date) ))||'-02-01')::date)

Bu doğru başlangıç ​​ve bitiş tarih kısıtlamaları üretir:

start_date  ;end_date    ;taken       ;year_taken
"1969-12-12";"1970-02-01";"1969-12-12";1969
"1969-12-12";"1970-02-01";"1969-12-13";1969
...
"1969-12-12";"1970-02-01";"1969-12-31";1969
"1970-12-12";"1971-02-01";"1970-12-12";1970

Bununla birlikte, 1970-01-01 ve 1970-02-01 eksik ancak arasındaki değerler dahil edilmelidir.

Versions

PostgreSQL 8.4.4 ve PHP 5.2.10

1 Cevap

SELECT
    m.amount
FROM
    measurement m,
    to_date('Dec 01 1900', 'Mon DD YYYY') AS A(d1),
    to_date('Feb 28 1900', 'Mon DD YYYY') AS B(d2),
    to_date('Feb 28 1901', 'Mon DD YYYY') AS C(d3)
WHERE m.taken
    BETWEEN
        d1 AND
        CASE WHEN d2 < d1 THEN d3 ELSE d2 END

Referanslar: conditional expressions, data type formatting functions.

EDIT: Üzgünüm, ben belirli bir yıl istediğini düşündüm:

SELECT
    amount
FROM
    (SELECT
        M.amount, M.taken,
        to_date('Dec 01 ' || extract(YEAR FROM M.taken), 'Mon DD YYYY'),
        to_date('Feb 28 ' || extract(YEAR FROM M.taken), 'Mon DD YYYY')
     FROM
        measurement AS M
     ) AS A(amount, taken, d1, d2)
WHERE
    (d2 >= d1 AND taken BETWEEN d1 AND d2)
    OR
    (d2 < d1 AND (taken <= d2 OR taken >= d1));

Set büyük ise, bu optimizasyon için şansını bir sürü yok. Bu durumda, daha sonra tüm belli bir yıl için tarih (say taken - (extract(year from taken) - 1900) * '1 year'::interval) ve 2 Aralık 1900 ve 28 Şubat 1900 ile karşılaştırmak dönüştüren bir SQL fonksiyonu olabilir. Eğer endeks bu tarihi dönüşüm fonksiyonun sonucu olabilir bu şekilde VE her giriş için iki tarih hesaplamak zorunda değilsiniz.

etiketler