'Prioritize' one column over another
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'
)