Geek asks and answers

Can I use a field as a “unit” in TIMESTAMPDIFF?

This is a question for another question , but I think it guarantees its own question:

Can I use the field as the "unit" parameter in TIMESTAMPDIFF()?

For example, here's a snippet:

SELECT foo FROM table t LEFT JOIN frequencies f USING (frequency_id)
WHERE MOD(TIMESTAMPDIFF(f.frequency_code, t.date, NOW()), t.frequency_num) = 0

This results in a syntax error at startup. Now, if I replaced f.frequency_codewith the actual word - MINUTE, DAYetc. - it works. But these are the words that are currently contained in f.frequency_code.

Is it possible to use the table field in this place?

+3
source share
2 answers

TIMESTAMPDIFF() does not support such dynamic units.

, CASE.

, , - :

SELECT foo 
FROM table t 
LEFT JOIN frequencies f USING (frequency_id)
WHERE MOD(
(CASE 
  WHEN f.frequency_code = 'MICROSECOND' THEN TIMESTAMPDIFF(MICROSECOND, t.date, NOW())
  WHEN f.frequency_code = 'SECOND' THEN TIMESTAMPDIFF(SECOND, t.date, NOW())
  WHEN f.frequency_code = 'MINUTE' THEN TIMESTAMPDIFF(MINUTE, t.date, NOW())
  WHEN f.frequency_code = 'HOUR' THEN TIMESTAMPDIFF(HOUR, t.date, NOW())
  WHEN f.frequency_code = 'DAY' THEN TIMESTAMPDIFF(DAY, t.date, NOW())
  WHEN f.frequency_code = 'WEEK' THEN TIMESTAMPDIFF(WEEK, t.date, NOW())
  WHEN f.frequency_code = 'MONTH' THEN TIMESTAMPDIFF(MONTH, t.date, NOW())
  WHEN f.frequency_code = 'QUARTER' THEN TIMESTAMPDIFF(QUARTER, t.date, NOW())
  WHEN f.frequency_code = 'YEAR' THEN TIMESTAMPDIFF(YEAR, t.date, NOW())
  END)
, t.frequency_num) = 0
+6

100%, , sql.

0

More articles:


All Articles