MySQL SELECT with IF and QUARTER using UNIX TIME

Question

I have a date that I'm trying to show as 'Quarters' and I can't make it show anything but NULL (or it errors). I want to end up with "2015 Q1" where endtime is currently 1422748800 (Feb 1st, 2015).

Here's my current attempt:

IF (
    stock.engine = 1, 
    concat(
        YEAR(endtime), ' Q', QUARTER(endtime)
        ) 
    , NULL) as QTR1

I've also tried fomatting endtime (which is in Unix format) as

DATE_FORMAT(FROM_UNIXTIME(endtime), '%Y')

It seems that no matter what I do, this same line keeps throwing an error OR it only shows as NULL. How can this be fixed so it shows correctly?

EDIT:

I don't know if this is relevant but I'll put it here anyway. The data in the table can have multiples... like so:

table: stock
userid      name                        engine     endtime
1           Robert Griffin                 1       1422748800
1           Robert Griffin                 2       1422752615
2           Daryl Jones                    4       1422939585
3           Patrick Daniels                3       1422858023
3           Patrick Daniels                4       1423047328

etc.

Answer 1

I think you need from_unixtime(). It looks like you dates are being stored in this format. So:

select (case when stock.engine = 1 
             then concat(year(from_unixtime(endtime)), ' Q', quarter(from_unixtime(endtime))
        end) as QTR1

Answer 2

Given that you are storing endtime as an integer, you can use the FROM_UNIXTIME() function to convert that to a datetime, and then use MySQL builtin functions on the datetime value.

As a demonstration:

SELECT CONCAT(DATE_FORMAT(FROM_UNIXTIME(1422748800),'%Y Q'),QUARTER(FROM_UNIXTIME(1422748800))) AS qtr

returns:

qtr      
-------
2015 Q1  

replace the literal integer values with a reference to your (integer) endtime column.