时间戳

首页 > 解决方案 > 如何处理 SQL (mySQL) 中的非标准日期格式？

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

问题描述

我是 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);

执行时出现以下错误：

第 21 行的 ERROR 1064 (42000)：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获得在“星期三）”附近使用的正确语法

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

到目前为止，我在网上发现的日期仍然或多或少包含标准日期格式，不像上面提到的那样定制（使用强制转换、解析、..）。

标签： mysqlsql

解决方案

您拥有的是NOW(),CURRENT_TIMESTAMP()和之类的功能CURRENT_DATE()。对于其他任何事情，LAST_WEDNESDAY()您都可以编写自己的存储函数。

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

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

更新

正如草莓所问 - 这是“更具可扩展性”的东西：

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 月）

看演示

我试图删除代码重复，但以这个结束：

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小提琴

推荐阅读