mysql - 如何处理 SQL (mySQL) 中的非标准日期格式？

Question

我是 SQL 的新手，目前正在学习。我正在使用安装在 Ubuntu 18.04 上的 MySQL。

我有下表:

CREATE TABLE IF NOT EXISTS product(name varchar(255) NOT NULL, 
                                   availability date NOT NULL);

我想在表中插入以下记录:

INSERT INTO `product` (`name`, `availability`) 
VALUES ('Title 1', last Wednesday);

执行时出现以下错误:

ERROR 1064 (42000) at line 21: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Wednesday)'

我知道这种格式(“上周三”、“下周”……)不是标准的日期格式。我想知道是否可以在 MySQL 中创建用户定义的类型来处理这些定制的日期记录。

到目前为止，我在网络上发现的日期仍然包含或多或少的标准日期格式，不像上面提到的那样定制(使用 cast、parse..)。

Answer 1

您拥有的是 NOW()、CURRENT_TIMESTAMP() 和 CURRENT_DATE() 等函数。对于诸如 LAST_WEDNESDAY() 之类的任何其他内容，您可以编写自己的 stored functions .

create function last_wednesday() returns date no sql
  return current_date() - interval (weekday(current_date()) + 4)%7+1 day;

或者在您的查询中使用相同的内联表达式。

更新

正如 Strawberry 所问 - 这是“更具可扩展性”的东西:

create function human_to_date(str text, date date) returns date no sql
  return case
    when str = 'last monday'    then date - interval (weekday(date) + 6-0)%7+1 day
    when str = 'last tuesday'   then date - interval (weekday(date) + 6-1)%7+1 day
    when str = 'last wednesday' then date - interval (weekday(date) + 6-2)%7+1 day
    when str = 'last thursday'  then date - interval (weekday(date) + 6-3)%7+1 day
    when str = 'last friday'    then date - interval (weekday(date) + 6-4)%7+1 day
    when str = 'last saturday'  then date - interval (weekday(date) + 6-5)%7+1 day
    when str = 'last sunday'    then date - interval (weekday(date) + 6-6)%7+1 day
  end
;

用作

select human_to_date('last wednesday', now())

或任何日期作为引用

select human_to_date('last sunday', '2019-10-01')

这将返回本月(2019 年 9 月)的最后一个星期日

参见 demo

我试图删除代码重复，但以这样结束:

delimiter //
create function human_to_date(str text, date date) returns date no sql
begin
  declare day_of_week int default null;
  if str rlike '^last (monday|tuesday|wednesday|thursday|friday|saturday|sunday)$' then
    set day_of_week = case substring_index(str, ' ', -1)
      when 'monday'    then 0
      when 'tuesday'   then 1
      when 'wednesday' then 2
      when 'thursday'  then 3
      when 'friday'    then 4
      when 'saturday'  then 5
      when 'sunday'    then 6
    end;
    return date - interval (weekday(date) + 6-day_of_week)%7+1 day;
  end if;
  return null;
end //
delimiter ;

db-fiddle