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?

  • @ypercube thanks I just typed the query in without running it. its edited – Gandalf StormCrow Oct 16 '14 at 18:00

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
   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' 
   between '2013-10-16 00:00:00.000000' and '2013-10-16 23:59:59.999999'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.