MySQL - Unix timestamp from columns with hour, day, month

Question

I have MySQL table with columns (day, month, hour). Is it possible to build unix timestamp from this within the query itself?

I am looking for something like this:

SELECT unix(2016, a.month, a.day, a.hour, 00, 00) as d
FROM a
WHERE d > 456456 AND d < 789789

Values 456456 and 789789 is provided from an application. I know, I can create unix timestamp from STR_TO_DATE, but then I have to create it for every comparison in WHERE clause as well, which I am afraid will be slow.

Create table:

CREATE TABLE `a` (
  `data_a` smallint(6) NOT NULL DEFAULT '0',  
  `hour` tinyint(4) NOT NULL,
  `day` tinyint(4) NOT NULL,
  `month` tinyint(4) NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

MySQL version 5.0

Answer 1

Instead of transforming the columns in the WHERE clause, do so with the constants given by the application. Those calculations only have to be done once.

We can write the condition in a short, compact form:

SELECT STR_TO_DATE(...)
FROM a 
WHERE  (2016, a.month, a.day, a.hour, 0, 0)
      >
      (  YEAR(FROM_UNIXTIME(456456)), 
        MONTH(FROM_UNIXTIME(456456)),
          DAY(FROM_UNIXTIME(456456)),
         HOUR(FROM_UNIXTIME(456456)),
       MINUTE(FROM_UNIXTIME(456456)),
       SECOND(FROM_UNIXTIME(456456))
      ) 
  AND  (2016, a.month, a.day, a.hour, 0, 0)
      <
      (  YEAR(FROM_UNIXTIME(789789)), 
        MONTH(FROM_UNIXTIME(789789)),
          DAY(FROM_UNIXTIME(789789)),
         HOUR(FROM_UNIXTIME(789789)),
       MINUTE(FROM_UNIXTIME(789789)),
       SECOND(FROM_UNIXTIME(789789))
      )

or in a long form, which has much more chances of using indexes effectively, in versions before 5.7:

SELECT STR_TO_DATE(...)
FROM a 
WHERE ((2016 > YEAR(FROM_UNIXTIME(456456)))
    OR (2016 = YEAR(FROM_UNIXTIME(456456))
        AND a.month > MONTH(FROM_UNIXTIME(456456)))
    OR (2016 = YEAR(FROM_UNIXTIME(456456))
        AND a.month = MONTH(FROM_UNIXTIME(456456))
        AND a.day > DAY(FROM_UNIXTIME(456456)))
    OR (2016 = YEAR(FROM_UNIXTIME(456456))
        AND a.month = MONTH(FROM_UNIXTIME(456456))
        AND a.day = DAY(FROM_UNIXTIME(456456))
        AND a.hour > HOUR(FROM_UNIXTIME(456456))))
AND   ((2016 < YEAR(FROM_UNIXTIME(789789)))
    OR (2016 = YEAR(FROM_UNIXTIME(789789))
        AND a.month < MONTH(FROM_UNIXTIME(789789)))
    OR (2016 = YEAR(FROM_UNIXTIME(789789))
        AND a.month = MONTH(FROM_UNIXTIME(789789))
        AND a.day < DAY(FROM_UNIXTIME(789789)))
    OR (2016 = YEAR(FROM_UNIXTIME(789789))
        AND a.month = MONTH(FROM_UNIXTIME(789789))
        AND a.day = DAY(FROM_UNIXTIME(789789))
        AND a.hour < HOUR(FROM_UNIXTIME(789789)))
    OR (2016 = YEAR(FROM_UNIXTIME(789789))
        AND a.month = MONTH(FROM_UNIXTIME(789789))
        AND a.day = DAY(FROM_UNIXTIME(789789))
        AND a.hour = HOUR(FROM_UNIXTIME(789789))
        AND 0 < MINUTE(FROM_UNIXTIME(789789))+SECOND(FROM_UNIXTIME(789789))))

This will also allow MySQL to use indexes on this query, if available. If not already done, you should create an index as (month, day, hour) (in this order!).

Answer 2

mysql> SELECT CAST(CONCAT('2016-', 5, '-', 31, ' ', 7, ':00:00') AS DATETIME);
+-----------------------------------------------------------------+
| CAST(CONCAT('2016-', 5, '-', 31, ' ', 7, ':00:00') AS DATETIME) |
+-----------------------------------------------------------------+
| 2016-05-31 07:00:00                                             |
+-----------------------------------------------------------------+

So, something like CAST(CONCAT('2016-', month, '-', day, ' ', hour, ':00:00') AS DATETIME)