SQL 피벗 및 문자열로 저장된 날짜 간의 차이 계산 - MySQL

SQL 피벗 및 문자열로 저장된 날짜 간의 차이 계산 - MySQL

테이블이 두 개 있어요.첫 번째는posts, 그리고 두 번째 것postmeta(누군가 눈치채면 저는 Wordpress DB를 사용하고 있습니다.이 작업을 위해 아는 것은 중요하지 않습니다.

posts테이블은 다음과 같습니다(이 때문에 생략).

ID  |  post_title | post_status | post_type
------------------------------------------
1   |  One        | publish     | hours
2   |  Two        | publish     | hours

postmeta테이블은 이렇게 생겼어요.날짜 형식은 d.m 입니다.Y. G:i:s.

meta_id | post_id | meta_key | meta_value
------------------------------------------
1       | 1       | from     | 1.1.2017. 10:00:00
2       | 1       | to       | 1.1.2017. 16:00:00
3       | 2       | from     | 2.1.2017. 12:00:00
4       | 2       | to       | 2.1.2017. 15:00:00

그 테이블에서ID=post_id원하는 결과는 아래의 표입니다.date_diff의 차이입니다.from그리고.toSQL에서 계산해야 하는 시간(date_diff = to - from)입니다.주의:meta_key로 정의됩니다.VARCHAR그리고.meta_value~하듯이LONGTEXT계산은 더 어려워집니다.

ID | title | from               | to                 | date_diff
------------------------------------------------------------------
1  | 1     | 1.1.2017. 10:00:00 | 1.1.2017. 16:00:00 | 6
2  | 1     | 2.1.2017. 12:00:00 | 2.1.2017. 15:00:00 | 3

지금 가지고 있는 암호는 이겁니다.열을 열로 만드는 것은 나에게 조금 문제가 되고 계산은 더욱 어려워.

SELECT posts.ID, posts.post_title, postmeta.meta_key, postmeta.meta_value 
FROM posts
INNER JOIN postmeta 
ON posts.ID = postmeta.post_id
WHERE post_status = 'publish' 
AND post_type = 'hours' 
AND (postmeta.meta_key = 'from' OR postmeta.meta_key = 'to');

정말 감사해요.:)

postmeta에서 self join을 사용하여 날짜 변환 문자열에 str_to_date를 사용하는 두 개의 구분된 컬럼을 가져올 수 있습니다.예: from)은 예약된 단어에 적절한 백틱을 사용하고 TIMESTAMPDIFF는 diff의 HOURS를 가져옵니다.

  SELECT 
          posts.ID
        , posts.post_title
        , str_to_date(table_from.meta_value, '%d.%m.%Y. %H:%i:%s')  as `from` 
        , str_to_date(table_to.meta_value, '%d.%m.%Y. %H:%i:%s' ) as `to` 
        , TIMESTAMPDIFF(HOUR, str_to_date(table_from.meta_value, '%d.%m.%Y. %H:%i:%s') , 
                                    str_to_date(table_to.meta_value, '%d.%m.%Y. %H:%i:%s' )) as diff
  FROM posts
  INNER JOIN postmeta table_from ON posts.ID = table_from.post_id and table_from.meta_key ='from'
  inner join postmeta table_to ON posts.ID = table_to.post_id and table_to.meta_key ='to'
  WHERE post_status = 'publish' 
  AND post_type = 'hours' 

소수점 이하에 사용할 수 있습니다.

diff로서 timestampdiff(MINUTE, startdate, enddate)/60

예: (Scaisedge의 방법보다 느리지만 읽기 쉬운...)

DROP TABLE IF EXISTS posts;

CREATE TABLE posts
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,post_title VARCHAR(12) NOT NULL
,post_status VARCHAR(12) NOT NULL
,post_type VARCHAR(12) NOT NULL
);

INSERT INTO posts VALUES
(1,'One','publish','hours'),
(2,'Two','publish','hours');

DROP TABLE IF EXISTS postmeta;

CREATE TABLE postmeta
(meta_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,post_id INT NOT NULL
,meta_key VARCHAR(12) NOT NULL
,meta_value VARCHAR(20) NOT NULL
);

INSERT INTO postmeta VALUES
(1,1,'from','1.1.2017. 10:00:00'),
(2,1,'to'  ,'1.1.2017. 16:00:00'),
(3,2,'from','2.1.2017. 12:00:00'),
(4,2,'to'  ,'2.1.2017. 15:00:00');

-- Date format is d.m.Y. H:i:s.

SELECT post_id
     , MAX(CASE WHEN meta_key = 'from' THEN STR_TO_DATE(meta_value,'%d.%m.%Y. %H:%i:%s') END) `from`
     , MAX(CASE WHEN meta_key = 'to' THEN STR_TO_DATE(meta_value,'%d.%m.%Y. %H:%i:%s') END) `to`
     , TIMEDIFF(
           MAX(CASE WHEN meta_key = 'to' THEN STR_TO_DATE(meta_value,'%d.%m.%Y. %H:%i:%s') END)
         , MAX(CASE WHEN meta_key = 'from' THEN STR_TO_DATE(meta_value,'%d.%m.%Y. %H:%i:%s') END)
               ) date_diff
  FROM postmeta 
 GROUP 
    BY post_id;

+---------+---------------------+---------------------+-----------+
| post_id | from                | to                  | date_diff |
+---------+---------------------+---------------------+-----------+
|       1 | 2017-01-01 10:00:00 | 2017-01-01 16:00:00 | 06:00:00  |
|       2 | 2017-01-02 12:00:00 | 2017-01-02 15:00:00 | 03:00:00  |
+---------+---------------------+---------------------+-----------+

마지막 부분은 독자들에게 쉬운 연습으로 남겨두었다

도움이 되었으면 좋겠어요.

SELECT 
   p.id, 
   p.post_title, 
   postmeta_temp.from, 
   postmeta_temp.to, 
   postmeta_temp.date_diff 
FROM   posts p 
   INNER JOIN (SELECT postmeta_from.post_id 
                      AS post_id, 
                      postmeta_from.meta_key 
                                     AS from, 
                      postmeta_to.meta_key 
                                     AS to, 
                      Date_diff(postmeta_to.meta_value, 
                      postmeta_from.meta_value) 
                                     AS date_diff 
               FROM   postmeta postmeta_from 

                      INNER JOIN postmeta postmeta_to 

                              ON postmeta_from.post_id = postmeta_to.post_id 

                                 AND postmeta_from.meta_key = 'from' 

                                 AND postmeta_to.meta_key = 'to') AS 

              postmeta_temp 

           ON p.id = postmeta_temp.post_id 

모든 아이디마다 다음번에는 같은 아이디와 다음 날짜로 확인하겠습니다.

select pm.post_id as ID, pm.post_id as title, pm.metavalue as from,  
(select x.metavalue from postmeta x where x.enter code herepost_id = pm.post_id and not x.meta_key = 'from') 
as to , 
(select cast(x.metavalue as timestamp) - cast(pm.metavalue as timestamp) from postmeta x 
      where x.post_id = pm.post_id and not x.meta_key = 'from') as date_diff from 
postmeta pm where pm.meta_key = 'from';


 id | title |        from        |         to         | date_diff 
----+-------+--------------------+--------------------+-----------
  1 |     1 | 1.1.2017. 10:00:00 | 1.1.2017. 16:00:00 | 06:00:00
  2 |     2 | 2.1.2017. 12:00:00 | 2.1.2017. 15:00:00 | 03:00:00

언급URL : https://stackoverflow.com/questions/44333429/sql-pivoting-and-calculating-difference-between-dates-stored-as-strings-mysql