如何将 mysql 中的日期与当前日期进行比较并发送通知电子邮件

Question

我的数据库条目有一个日期列（ mySQL 日期类型）。我想每月将该日期与当前服务器日期以及该日期之前的 3 天进行比较，以自动向指定地址发送电子邮件。

Answer 1

您还可以使用更简单的 MySQL TO_DAYS() 函数：

SELECT email FROM table WHERE TO_DAYS(date)=To_DAYS(NOW())-3;

Answer 2

Well this is just a mock but you could try something like this:

$localTime = `date +"%Y/%m/%d %H:%M:%S"`

// Query MySQL to get datetime

// compare the two times and send email

Answer 3

您可以让 PHP 或 MySQL 为您做这些脏活，无论是脚本还是 SELECT 语句。

<?php
// We shall assume that you have a array called $send_date with values in the format YYYY-MM-DD (MySQL Date Format).
// These values will be the values in the date column, and this script will send the email 3 days before those dates.

$today = date("U");
// Work out, in seconds, the amount of time beforehand you would like to send the email.
$warning = 60 * 60 * 24 * 3;
foreach($send_date as $timestamp)
 {
  $sd = strtotime($timestamp);
  // If $sd is not an integer, it is not a valid timestamp. Move onto the next.
  if(!is_int($timestamp)) continue;
  $diff = $timestamp - $today;
  if($diff > 0 && $diff <= $warning)
   {
    function_to_send_your_email($row_id_of_this_particular_date);
   }
 }

或者一个 SELECT 语句：

$warning = 60 * 60 * 24 * 3;
$today = date("U");
$sql = 'SELECT * FROM your_table WHERE TIMESTAMP(date_column) BETWEEN '.$today.' AND '.$today + $warning.';';
// Perform query, and act on results brought back.

这个脚本以及一般的 PHP 的唯一问题是，只有在脚本运行时，它才会提前检查三天（或 $warning 中指定的值） 。如果你想让它继续检查，我建议你设置一个 CRON 作业。
我不能保证其中任何一个都能工作，因为我还没有检查过它们。

希望这能给您一些关于此事的启发！