2

This is a followup to a another question, but I think warrants its own question:

Can I use a 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 gets a syntax error when I run it. Now, if I replace f.frequency_code with an actual word - MINUTE, DAY, etc. - it works. But those are exactly the words currently contained in f.frequency_code.

Is it simply not doable to be able to use a table field in that location?

Community
  • 1
  • 1
Andrew
  • 5,095
  • 6
  • 42
  • 48
  • Without having looked into the MySQL source to see what's truly acceptable, I'm going to guess no - if you get a syntax error, then obviously mysql isn't expecting to get a fieldname in that place. One possible alternative is to try a subquery which returns the interval name, but I doubt that'd work either. Most likely MySQL's parser is expecting to find a literal interval name, and only that literal interval name. – Marc B Jun 10 '11 at 14:19
  • You could always work with a nested `IF` construction but that would produce terrible code. Interested to see whether anything comes up - although it's possible it's not possible – Pekka Jun 10 '11 at 14:19

2 Answers2

5

TIMESTAMPDIFF() does not support dynamic units like that.

Since you're working with a known set of units, you could use a CASE statement to achieve this.

Starting with your example query, something like this would probably work:

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
Ike Walker
  • 64,401
  • 14
  • 110
  • 109
0

Not 100%, but you might manage to work around the issue by creating a function that prepares and executes dynamic sql.

Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154