3

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

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!).

  • 1
    You are right that to use indexes, the reverse transformation has to be done. But your transformation is not correct (i.e. equivalent). Since it seems you know that, why don't you edit and correct it so it works always and not in the limited case you mention (same day)? – ypercubeᵀᴹ Oct 23 '16 at 14:47
  • @ypercube I now did, but didn't test it. You may want to look through it, whether you spot any errors in the logic. – wolfgangwalther Oct 23 '16 at 21:42
  • No. FROM_UNIXTIME(456456) is 1970-01-05 22:47:36. It would be coincidence if you get the correct MONTH from that. – Rick James Oct 24 '16 at 2:04
  • "Index merge" is rarely used by the Optimizer. – Rick James Oct 24 '16 at 2:05
  • 1
    I don't think that index merge is even relevant for this query. It's more relevant to Row constructors but there is no similar page in 5.0 docs so I'm not sure how it will behave. In any case, using AND/OR is probably better than the compact form. – ypercubeᵀᴹ Oct 24 '16 at 8:27
-1
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)

  • How much difference is there between casting a string to datetime and using STR_TO_DATE? – wolfgangwalther Oct 23 '16 at 21:49
  • It would probably work with just the CONCAT. – Rick James Oct 24 '16 at 2:02
  • You don't need CAST, because OPs questions was targeting a unix timestamp as a result. So you would have to put the whole thing into a UNIX_TIMESTAMP() call, which takes a datetime string as an argument and does a string-to-timestamp conversion anyway. But that was not OPs question. OP wanted to know, whether there was any alternative to creating that unix timestamp for every row in the table for the WHERE part - and thus having to do a full table scan. – wolfgangwalther Oct 24 '16 at 6:57
  • But since you posted your CAST(CONCAT(...)) as an alternative to OPs STR_TO_DATE (which can do more string to datetime conversions than the cast), I wanted to know whether there were any difference between those two approaches in this case, that you had in mind. – wolfgangwalther Oct 24 '16 at 6:59
  • STR_TO_DATE() is a more complex function. However, function time is usually insignificant compared to the time taken to fetch rows. Mostly I wanted to bring out that it could be done in one line, not several. – Rick James Oct 24 '16 at 18:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.