主页 / coding / 问题 / 76938193
Accepted
sahil
Asked: 2023-08-20 14:25:43 +0800 CST

编写一个 psql 查询来查找某个时间间隔内的用户

  • 772

我有用户和团队表。

  • 用户表有month, day, id, timezone and team_id
  • 团队表有time (24 hour format) and id

我必须找到本地时区时间等于其团队指定时间的所有用户。我的cron runs every 15 minute所以我想确保没有用户被遗漏。如何为此编写 postgres 查询。我已经写了下面的查询，但我想扩展它，以便它可以处理上面提到的所有情况

Cron 在 Nodejs 中运行

SELECT id, timezone
FROM user_table
WHERE EXTRACT(HOUR FROM NOW() AT TIME ZONE 'UTC' AT TIME ZONE user_table.timezone) = 9
  AND day = EXTRACT(DAY FROM NOW() AT TIME ZONE 'UTC' AT TIME ZONE user_table.timezone)
  AND month = EXTRACT(MONTH FROM NOW() AT TIME ZONE 'UTC' AT TIME ZONE user_table.timezone);
postgresql

1 个回答

  1. Best Answer

    如果我的表结构和依赖关系正确，那么这应该可以工作。

    首先创建一个表来跟踪收到愿望的用户：

    CREATE TABLE wished_users (
  user_id INT PRIMARY KEY,
  wish_sent_at TIMESTAMPTZ
);

    然后使用查询选择符合条件的用户并将其插入到 Wished_users 表中，同时考虑时间范围：

    WITH eligible_users AS (
  SELECT
    u.user_id,
    u.username,
    u.birthdate,
    u.timezone
  FROM user_table u
  JOIN team_table t ON u.team_id = t.id
  WHERE
    EXTRACT(DAY FROM u.birthdate AT TIME ZONE u.timezone) = EXTRACT(DAY FROM CURRENT_TIMESTAMP AT TIME ZONE u.timezone)
    AND EXTRACT(MONTH FROM u.birthdate AT TIME ZONE u.timezone) = EXTRACT(MONTH FROM CURRENT_TIMESTAMP AT TIME ZONE u.timezone)
    AND EXTRACT(HOUR FROM CURRENT_TIMESTAMP AT TIME ZONE u.timezone) = 8
    AND EXTRACT(MINUTE FROM CURRENT_TIMESTAMP AT TIME ZONE u.timezone) >= 45
    AND EXTRACT(HOUR FROM NOW() AT TIME ZONE u.timezone) = 9
    AND NOT EXISTS (
      SELECT 1 FROM wished_users wu WHERE wu.user_id = u.user_id
    )
)
INSERT INTO wished_users (user_id, wish_sent_at)
SELECT user_id, CURRENT_TIMESTAMP
FROM eligible_users;
    • 0

相关问题