'Prioritize' one column over another

Question

I'm working with subscription table currently and I'm creating a cron job that will take records based on some condition from the subscription table.

I want to select all subscription records that have either been renewed_at or created_at (these are column names) on certain date, seems easy so I do it like this, if the date is 2013-10-16 :

select * from subscription where
(subscription.renewed_at between '2013-10-16 00:00:00.000000'
and '2013-10-16 23:59:59.999999') OR 
(subscription.created_at between 2013-10-16 00:00:00.000000' 
and '2013-10-16 23:59:59.999999')

So here is what I want to be sure, and I'm not sure how to do it.

I want to prioritize renewed_at column over created_at column, in a sense that if somebody renewed their subscription at a certain date that I should select the record based on that date rather than on created_at date. In case however the renewed_at is not there I should select the record based on the created_at date.

And I should not select the record based on created_at if the renewed_at column is in there, and they're always different in sense than they're not ever the same date even if they're both present. created_at is always present.

Is is possible to do it? Or am I thinking this the wrong way?

Answer 1

I think this might work, assuming that by not present you mean is NULL:

select * from subscription where
coalesce ( subscription.renewed_at, subscription.created_at ) 
between '2013-10-16 00:00:00.000000' and '2013-10-16 23:59:59.999999')

The coalesce() function returns its first non-NULL argument, so if renewed_at is present it will be returned, otherwise created_at will be the result.

Alternatively, you could use this, which will work in case not present means something else in your table, e.g. the value 9999-01-01-00-00-00:

select * from subscription where
(subscription.renewed_at 
   between '2013-10-16 00:00:00.000000' and '2013-10-16 23:59:59.999999')
or ( 
subscription.renewed_at = '9999-01-01-00-00-00' 
and 
subscription.created_at 
   between '2013-10-16 00:00:00.000000' and '2013-10-16 23:59:59.999999'
)