如何在postgre SQL DB的两个日期之间获取记录？ [英] How do I get records between 2 dates from postgre SQL DB?

问题描述

我在Postgre SQL中有数据库。

我想为今天五天后pwd过期的所有用户生成电子邮件通知。



我怎样才能从DB获取从今天到接下来5天过期的pwd记录。

这个过程应该适用于每一天。

I have DB in Postgre SQL.
I want to generate the an email notification for all users whose pwd was expired with five days from today.

How can I get the records from DB for pwd expired from today to next 5 days.
This proess should work for every day.

推荐答案

您可以在sql中对此进行编码，如下所示：

You can code this in sql like this:

DECLARE
   -- Cursor of those accounts whose password 
   -- expires in five days or less
   CURSOR c1 IS SELECT
u.username,u.expiry_date,e.address
      FROM dba_users u, email_address e
      WHERE u.username=e.username
      AND expiry_date-sysdate<=5;
   username VARCHAR2(50);
   email    VARCHAR2(250);
   expire_date DATE;
   -- Who is sending the email?
   sender    VARCHAR2(40) := '[e-mail]';
   -- What is the message body of the email?
   message   VARCHAR2(200);
   -- What is the SMTP gateway?
   mailhost  VARCHAR2(30) := 'myserver.host.dom';
   -- What is the gateway port?
   mailport  NUMBER := 25;
   -- Define the SMTP gateway connection
   mail_conn UTL_SMTP.connection;
BEGIN
   -- set up email message
   message:=', your password will expire on';

   OPEN c1;
   LOOP
      FETCH c1 INTO username,email,expire_date;
      EXIT WHEN c1%NOTFOUND;

      -- Open the SMTP gateway connection
      mail_conn := UTL_SMTP.open_connection(mailhost,
mailport);
      -- Perform initial handshaking after connecting
      UTL_SMTP.helo(mail_conn, mailhost);
      -- Initiate a mail transaction with the server
      UTL_SMTP.mail(mail_conn, sender);
      -- Specify the recipient
      UTL_SMTP.rcpt(mail_conn, email);
      -- Specify the message body
      UTL_SMTP.data(mail_conn, username||message||'
'||expire_date);
      -- Terminate and disconnect. This sends the
email.
      UTL_SMTP.quit(mail_conn);

   END LOOP;
   CLOSE c1;
END;

这篇关于如何在postgre SQL DB的两个日期之间获取记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！