可变日期格式查询

Question

我试图将从PostgreSQL表中提取的字符串（使用索引聚合数组）转换为正确格式化的日期进行查询。我的问题是我的日期格式不同，包括YYYY、Mon-yyy和DD-Mon-y。我的计划是创建日期范围，以包含由模糊日期表示的所有可能时间。例如，“2000”将转换为“2000年1月1日”和“2000年12月31日”，并根据自定义输入日期范围进行测试。同样，“2014年2月”也将改为“2014年1月1日”和“2014年2日28日”。（注：我目前无法想出一种方法来计算闰年。）

我目前使用的方法包括简单的字符串连接。然而，我需要能够区分字符串长度(这将表示日期格式)，并且我正在努力将条件表达式合并到我的查询中。以下是我目前掌握的情况:

SELECT a.accession, string_agg(b.value, ' | ') AS bvalue_list, c.name, d.description, string_agg(e.value, ' | ') AS evalue_list, f.seqlen, f.residues 
FROM dbxref a INNER JOIN dbxrefprop b ON a.dbxref_id = b.dbxref_id
INNER JOIN biomaterial d ON b.dbxref_id = d.dbxref_id
INNER JOIN feature f ON d.dbxref_id = f.dbxref_id
INNER JOIN biomaterialprop e ON d.biomaterial_id = e.biomaterial_id
INNER JOIN contact c ON d.biosourceprovider_id = c.contact_id
GROUP BY a.accession, c.name, d.description, f.seqlen, f.residues
HAVING ((array_agg(b.value))[5] = 'source018' OR (array_agg(b.value))[5] = 'source015')
AND to_date('04 Jan ' || (array_agg(e.value))[3], 'DD Mon YYYY') BETWEEN '01 Jan 1999' AND '31 Jan 2000';

我为疯狂的查询声明道歉。我想完整地展示这条语句，因为它的怪癖（信息是由“HAVING”子句而不是“where”子句处理的）。相关部分（最下面的行）归结为选择以字符串格式表示时间的索引聚合。我的搜索条件有意筛选出不符合使用的串联方法的日期（不包括Mon-YYYY和DD Mon-YYy日期）。我一直在尝试合并一个“CASE”条件，但我不确定是否/如何将其适合现有查询。

问题的简化

我需要修改以下查询：

SELECT e.biomaterial_id, string_agg(e.value, ' | ') AS evalue_list
FROM biomaterialprop e
GROUP BY e.biomaterial_id;

它产生：

 biomaterial_id |                  evalue_list                   
----------------+------------------------------------------------
              8 | NULL | Feb 2002 | Canada | T2
              4 | NULL | 03 Mar 2008 | Hainan, China | T2
              5 | nasal swab | Oct 2010 | Fujian, China | T1
             11 | nasal swab | 10 Apr 2014 | Nebraska, USA | T1
              3 | lung tissue | 01 Jan 2005 | Nebraska, USA | T2
             10 | lung tissue | 2005 | USA | T2
              9 | serum | 2001 | Ohio, USA | T1
              6 | serum | 2000 | Utah, USA | T1
              2 | serum | 01 Jan 2005 | Iowa, USA | T1
              7 | NULL | 02 Aug 1998 | Alberta, Canada | T2

我可以通过索引（array_agg（e.value））[3]来选择日期字段。接下来，我需要修改日期字符串并将其插入到单独的输出列中。我认为它应该看起来像这样（目前不起作用）：

SELECT e.biomaterial_id, string_agg(e.value, ' | ') AS evalue_list,
  CASE char_length((array_agg(e.value))[3])
    WHEN 11 
    THEN to_date((array_agg(e.value))[3], 'DD Mon YYYY')
    WHEN 8 
    THEN to_date('01 ' || (array_agg(e.value))[3], 'DD Mon YYYY')
    ELSE to_date('01 Jan ' || (array_agg(e.value))[3], 'DD Mon YYYY')
  END
  AS date1
  CASE char_length((array_agg(e.value))[3])
    WHEN 11 
    THEN to_date((array_agg(e.value))[3], 'DD Mon YYYY')
    WHEN 8 
    THEN last_day(to_date('01 ' || (array_agg(e.value))[3], 'DD Mon YYYY'))
    ELSE to_date('31 Dec ' || (array_agg(e.value))[3], 'DD Mon YYYY')
  END
  AS date2
FROM biomaterialprop e
GROUP BY e.biomaterial_id, date1, date2;

我试图从postgresql中的堆栈post: IF-THEN-ELSE语句中复制答案的查询结构

Edit1-已经有好几个月了，我想我至少在SQL方面有一点能力。尽管如此，我真的不满意这个旧的解决方案。任何其他建议或解决方案都会有所帮助。

演示包含时间信息的表格:

specimen_collection_date 
--------------------------
 01-Nov-2013
 2013
 2012
 04-Jul-2013
 16-Jan-2011
 Jan-2011
 2001
 Nov-2005

Answer 1

简化问题的答案

代码：

create or replace function last_day(date) returns date as 'select 
cast(date_trunc(''month'', $1) + ''1 month''::interval as date) - 1' 
language sql;

SET search_path = chado;
SELECT specimen_collection_date,
  CASE
    WHEN char_length(specimen_collection_date) = 11
    THEN to_date(specimen_collection_date, 'DD Mon YYYY')
    WHEN char_length(specimen_collection_date) = 8 
    THEN to_date('01 ' || specimen_collection_date, 'DD Mon YYYY')
    ELSE to_date('01 Jan ' || specimen_collection_date, 'DD Mon YYYY')
  END
  AS date1,
  CASE
    WHEN char_length(specimen_collection_date) = 11 
    THEN to_date(specimen_collection_date, 'DD Mon YYYY')
    WHEN char_length(specimen_collection_date) = 8 
    THEN last_day(to_date('01 ' || specimen_collection_date, 'DD Mon YYYY'))
    ELSE to_date('31 Dec ' || specimen_collection_date, 'DD Mon YYYY')
  END
  AS date2
FROM prrsv_search_mv WHERE specimen_collection_date != '';

输出：

 specimen_collection_date |   date1    |   date2    
--------------------------+------------+------------
 01-Nov-2013              | 2013-11-01 | 2013-11-01
 2013                     | 2013-01-01 | 2013-12-31
 2012                     | 2012-01-01 | 2012-12-31
 04-Jul-2013              | 2013-07-04 | 2013-07-04
 16-Jan-2011              | 2011-01-16 | 2011-01-16
 Jan-2011                 | 2011-01-01 | 2011-01-31
 2001                     | 2001-01-01 | 2001-12-31
 Nov-2005                 | 2005-11-01 | 2005-11-30

因为PostgreSQL没有last_day函数，所以它必须是定制的（来源：https://www.postgresql.org/message-id/[e-mail]）。

Edit1-更新当前答案以匹配最近的问题编辑。