Clever Geek Handbook

Mysql datetime range selection

I have a recurring payment mode in the system. All is well, a successful API response is logged in the database. I'm worried about the question: I'm going to automate the subsequent payment steps (unfortunately, the payment gateway does not support) the system. Through cron, I plan on checking which accounts should be included in the follow-up process and notification. I have a snippet of a database record:

+---------------------+---------------------+--------------------+
| 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'm having a hard time creating a SQL query for this. Assuming today is 2012-05-16 and the time is 07:00:00 . I want to get all accounts that are today or less than the current time. For example, the only valid account (based on the current date and time I have provided ) I need is account 1-69010235 .

Also, any hints if at what interval should I set up my cron?

+3


source to share


3 answers


This query will return all records that are expiring today -

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

If you want all accounts to expire today but less than the current time -



SELECT *
FROM accounts
WHERE payment_expirydate BETWEEN CURRENT_DATE AND CURRENT_TIMESTAMP

To implement codeigniter AR you will be able to use -

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


source 


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 jump to curl.



sqlfiddle here .

+3


source


If you want all the records in the table you are showing are valid for the current time - and valid means payment_date is earlier than now and payment_expirydate is later, you can use:

SELECT transaction_number from `table`
WHERE payment_date > now()
AND payment_expirydate < now();

Does this get what you want?

+1


source






More articles:


All Articles