Combine Date and Time Fields and Order By DateTime Field

January 29, 2022

I would like to combine the date and time for meta values performance_date_1 and performance_time_1 and then sort by this new DATETIME value. Here is my SQL query so far, but no luck.

Query SQL:

SELECT TIMESTAMP(performance_date_1, performance_time_1) as DateTimeTS FROM test ORDER BY DateTimeTS;

Because of how the table is structured I am having a hard time accomplishing this. I am not sure if the Table needs to be pivoted first GROUP BY post_id column.

Schema SQL

CREATE TABLE test (
  meta_id bigint(20) AUTO_INCREMENT,
  post_id INT,
  meta_key varchar(255) NULL,
  meta_value longtext NULL,
  PRIMARY KEY (`meta_id`)
);
INSERT INTO test (post_id, meta_key, meta_value) VALUES (500, 'performance_date_1', '20220405');
INSERT INTO test (post_id, meta_key, meta_value) VALUES (500, 'performance_time_1', '21:00:00');
INSERT INTO test (post_id, meta_key, meta_value) VALUES (500, 'performance_date_2', '20220407');
INSERT INTO test (post_id, meta_key, meta_value) VALUES (500, 'performance_time_2', '22:00:00');

INSERT INTO test (post_id, meta_key, meta_value) VALUES (501, 'performance_date_1', '20220403');
INSERT INTO test (post_id, meta_key, meta_value) VALUES (501, 'performance_time_1', '20:00:00');
INSERT INTO test (post_id, meta_key, meta_value) VALUES (501, 'performance_date_2', '20220407');
INSERT INTO test (post_id, meta_key, meta_value) VALUES (501, 'performance_time_2', '19:00:00');

SQL Fiddle
https://www.db-fiddle.com/f/rSwxZKvBQ2JiwW5H6KawoW/1

>Solution :

You’re correct. You should make a pivot table.

SELECT
    post_id,
    MAX( CASE meta_key WHEN"performance_date_1"THEN meta_value ELSE NULL END ) AS date_1,
    MAX( CASE meta_key WHEN"performance_time_1"THEN meta_value ELSE NULL END ) AS time_1,
    MAX( CASE meta_key WHEN"performance_date_2"THEN meta_value ELSE NULL END ) AS date_2,
    MAX( CASE meta_key WHEN"performance_time_2"THEN meta_value ELSE NULL END ) AS time_2,
    TIMESTAMP(
        MAX( CASE meta_key WHEN"performance_date_1"THEN meta_value ELSE NULL END ),
        MAX( CASE meta_key WHEN"performance_time_1"THEN meta_value ELSE NULL END )
    ) AS datetime_1,
    TIMESTAMP(
        MAX( CASE meta_key WHEN"performance_date_2"THEN meta_value ELSE NULL END ),
        MAX( CASE meta_key WHEN"performance_time_2"THEN meta_value ELSE NULL END )
    ) AS datetime_2
FROM
    test
GROUP BY
    post_id
ORDER BY
    datetime_1;