mysql datetime range select

Question

I have a recurring payment mode in the system. All is well, API successful response is logged on the database. The concern of my question is, I'm about to automate the subsequent payment action (which unfortunately, the payment gateway doesn't support) of the system. Via cron I'll schedule a check on whether which accounts must be included in the subsequent process and notice. I have here a snip of the database entry:

+---------------------+---------------------+--------------------+
| payment_date        | payment_expirydate  | transaction_number |
+---------------------+---------------------+--------------------+
| 2012-02-14 03:47:15 | 2012-05-14 03:47:15 | 1-67815163         |
| 2012-02-16 00:53:03 | 2012-05-16 00:53:03 | 1-69010235         |
| 2012-02-16 08:57:16 | 2012-05-16 08:57:16 | 1-69027483         |
| 2012-02-16 09:08:06 | 2012-05-16 09:08:06 | 1-69027694         |
| 2012-02-16 09:58:17 | 2012-05-16 09:58:17 | 1-69028921         |
| 2012-02-17 09:28:32 | 2012-05-17 09:28:32 | 1-69072076         |
| 2012-02-17 06:17:45 | 2012-05-17 06:17:45 | 1-69068200         |
| 2012-02-17 11:12:08 | 2012-05-17 11:12:08 | 1-69074788         |
+---------------------+---------------------+--------------------+

I am having a difficulty in creating the SQL query for this. Assuming, the date today is 2012-05-16 and the time is 07:00:00. I want to get all the accounts which is today and less than the current time. For instance, the only valid account (based on the current date and time I indicated) I need is account 1-69010235.

Also, any tips if on what interval should I set my cron to run?

Answer 1

This query will return all records that expire today -

SELECT *
FROM accounts
WHERE payment_expirydate BETWEEN CURRENT_DATE AND (CURRENT_DATE + INTERVAL 1 DAY - INTERVAL 1 SECOND)

If you want all accounts expiring today but less than current time -

SELECT *
FROM accounts
WHERE payment_expirydate BETWEEN CURRENT_DATE AND CURRENT_TIMESTAMP

For codeigniter's AR implementation you should be able to use -

$this->db->where('payment_expirydate BETWEEN CURRENT_DATE AND CURRENT_TIMESTAMP', NULL, FALSE);

Answer 2

SELECT group_concat(transaction_number) as transaction_number
FROM tab
WHERE payment_date BETWEEN CURRENT_DATE AND CURRENT_TIMESTAMP

You can use this to return a string and use perl to split the string and pass to curl.

sqlfiddle here.