时间戳

首页 > 解决方案 > MYSQL + php，避免日期冲突

php - MYSQL + php，避免日期冲突

问题描述

我有一个数据库，其中包含诸如“狂欢节......

+-------------------+------------+---------------+-----------------------+
+   Name            +    Date    + action starts + action duration (min) + 
+-------------------+------------+---------------+-----------------------+
+ Carneval          + 2018-04-30 +   12:00:00    +          150          +
+-------------------+------------+---------------+-----------------------+
+ Fairytale evening + 2018-04-30 +   20:00:00    +          200          +
+-------------------+------------+---------------+-----------------------+

我如何避免在 2018 年 4 月 30 日的 11:58:00 或 13:00:00 将诸如“尽你所能”之类的动作放入数据库？因为在本例中，这些时间显然与其他动作（如狂欢节）相冲突。 .

我正在使用 PHP 在表中插入数据...

标签： phpmysql

解决方案

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(event_id SERIAL PRIMARY KEY
,name VARCHAR(12) NOT NULL 
,start_dt DATETIME NOT NULL 
,end_dt DATETIME NULL
);

INSERT INTO my_table VALUES
(1, 'Carnival', '2018-04-30 12:00:00','2018-04-30 12:00:00'+INTERVAL 150 MINUTE),
(2, 'Fairytale','2018-04-30 20:00:00','2018-04-30 20:00:00'+INTERVAL 200 MINUTE);


SELECT * FROM my_table;
 +----------+-----------+---------------------+---------------------+
 | event_id | name      | start_dt            | end_dt              |
 +----------+-----------+---------------------+---------------------+
 |        1 | Carnival  | 2018-04-30 12:00:00 | 2018-04-30 14:30:00 |
 |        2 | Fairytale | 2018-04-30 20:00:00 | 2018-04-30 23:20:00 |
 +----------+-----------+---------------------+---------------------+

INSERT INTO my_table (name,start_dt,end_dt) 
SELECT 'Whatever','2018-04-20 11:58:00','2018-04-30 13:00' 
  FROM (SELECT 1) x 
  LEFT 
  JOIN my_table y 
    ON y.end_dt > '2018-04-30 11:58:00' 
   AND y.start_dt < '2018-04-30 13:00:00' 
 WHERE y.event_id IS NULL;

SELECT * FROM my_table;
 +----------+-----------+---------------------+---------------------+
 | event_id | name      | start_dt            | end_dt              |
 +----------+-----------+---------------------+---------------------+
 |        1 | Carnival  | 2018-04-30 12:00:00 | 2018-04-30 14:30:00 |
 |        2 | Fairytale | 2018-04-30 20:00:00 | 2018-04-30 23:20:00 |
 +----------+-----------+---------------------+---------------------+

推荐阅读