MySQL using table columns in function to create alias to be used in sorting

Question

It sounds more complicated than it actually is. Here is what I'm trying to do within the SELECT part:

SELECT TIMESTAMPADD(
          UCASE(
                 SUBSTRING(offset_unit,1,CHAR_LENGTH(offset_unit)-1)
                ),1,'2003-01-02') as offset_date

offset_unit is a VARCHAR column in the database. It contains one of the following: "Hours","Minutes".

offset is an INT.

I am trying to convert the offset_unit to uppercase, after I have removed the last character ('s') so I can have a proper interval (MINUTE, HOUR...) so I can get a date that I can use in sorting afterwards, but MySQL keeps throwing an error. I have tested each step by adding one function at a time, and it only fails after I add TIMESTAMPADD. If I enter MINUTE manually then it works.

Any way to get this working?

Additional info: I am running this in CakePHP 1.3, in a find, within the 'fields' array, but that shouldn't be important.

What is the MySQL error you get? Why are there backslashes in your query? — Jocelyn
– Jocelyn, Commented Aug 23, 2012 at 11:10
@Jocelyn just the standard "You have an error in your SQL syntax", nothing helpful. Oops, the backslashes are from the PHP code, editing them out now. — Nick Zinger
– Nick Zinger, Commented Aug 23, 2012 at 11:26

davek · Accepted Answer · 2012-08-23 11:36:06Z

3

this can be easily achived by using CASE WHEN clause as:

SELECT (CASE 
            WHEN offset_unit = 'HOURS' 
                   THEN TIMESTAMPADD(HOUR,`offset`,'2003-01-02')
            WHEN offset_unit = 'MINUTES' 
                   THEN TIMESTAMPADD(MINUTE,`offset`,'2003-01-02')
        END) AS offset_date
FROM my_table;

SEE SQLFIDDLE DEMO HERE

edited Aug 23, 2012 at 11:36

davek

23k11 gold badges80 silver badges97 bronze badges

answered Aug 23, 2012 at 11:23

Omesh

29.4k6 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ekholm · Accepted Answer · 2012-08-23 11:22:53Z

1

It doesn't work because TIMESTAMPADD does not take a string as the first argument, but a unit keyword, for example MINUTE. My guess is that you need to do this in two steps, first get the unit and then construct a query with the correct keyword.

answered Aug 23, 2012 at 11:22

ekholm

2,57319 silver badges18 bronze badges

Collectives™ on Stack Overflow

MySQL using table columns in function to create alias to be used in sorting

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related