- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我有一个查询要根据按 date
和 employee_id
分组的日期系列生成报告。日期应基于特定时区,在本例中为“Asia/Kuala_Lumpur”。但这可能会根据用户所在的时区而改变。
SELECT
d::date AT TIME ZONE 'Asia/Kuala_Lumpur' AS created_date,
e.id,
e.name,
e.division_id,
ARRAY_AGG(
a.id
) as rows,
MIN(a.created_at) FILTER (WHERE a.activity_type = 1) as min_time_in,
MAX(a.created_at) FILTER (WHERE a.activity_type = 2) as max_time_out,
ARRAY_AGG(
CASE
WHEN a.activity_type = 1
THEN a.created_at
ELSE NULL
END
) as check_ins,
ARRAY_AGG(
CASE
WHEN a.activity_type = 2
THEN a.created_at
ELSE NULL
END
) as check_outs
FROM (SELECT MIN(created_at), MAX(created_at) FROM attendance) AS r(startdate,enddate)
, generate_series(
startdate::timestamp,
enddate::timestamp,
interval '1 day') g(d)
CROSS JOIN employee e
LEFT JOIN attendance a ON a.created_at::date = d::date AND e.id = a.employee_id
where d::date = date '2020-11-20' and division_id = 1
GROUP BY
created_date
, e.id
, e.name
, e.division_id
ORDER BY
created_date
, e.id;
attendance
表的定义和示例数据:
CREATE TABLE attendance (
id int,
employee_id int,
activity_type int,
created_at timestamp with time zone NOT NULL
);
INSERT INTO attendance VALUES
( 1, 1, 1,'2020-11-18 07:10:25 +00:00'),
( 2, 2, 1,'2020-11-18 07:30:25 +00:00'),
( 3, 3, 1,'2020-11-18 07:50:25 +00:00'),
( 4, 2, 2,'2020-11-18 19:10:25 +00:00'),
( 5, 3, 2,'2020-11-18 19:22:38 +00:00'),
( 6, 1, 2,'2020-11-18 20:01:05 +00:00'),
( 7, 1, 1,'2020-11-19 07:11:23 +00:00'),
( 8, 1, 2,'2020-11-19 16:21:53 +00:00'), <-- Asia/Kuala_Lumpur +8 should be in 20.11 (refer to the check_outs field in the results output)
( 9, 1, 1,'2020-11-19 19:11:23 +00:00'), <-- Asia/Kuala_Lumpur +8 should be in 20.11 (refer to the check_ins field in the results output)
(10, 1, 2,'2020-11-19 20:21:53 +00:00'), <-- Asia/Kuala_Lumpur +8 should be in 20.11 (refer to the check_outs field in the results output)
(11, 1, 1,'2020-11-20 07:41:38 +00:00'),
(12, 1, 2,'2020-11-20 08:52:01 +00:00');
这是一个fiddle进行测试。
查询不包括时区 Asia/Kuala_Lumpur +8 的输出中的第 8-10 行,尽管它应该包括在内。结果显示“行”字段 11,12
。
如何修复查询,使其根据给定时区的日期生成报告? (这意味着我可以将 Asia/Kuala_Lumpur
更改为 America/New_York
等)
我被告知要做这样的事情:
where created_at >= timestamp '2020-11-20' AT TIME ZONE 'Asia/Kuala_Lumpur'
and created_at < timestamp '2020-11-20' AT TIME ZONE 'Asia/Kuala_Lumpur' + interval '1 day'
但我不确定如何应用它。在 this fiddle 中似乎无法正常工作.它应该包括第 8、9、10、11、12 行,但只显示第 8、9、10 行。
最佳答案
考虑对您的设置进行一些修改:
CREATE TABLE employee (
id int PRIMARY KEY -- !
, name text -- do NOT use char(n) !
, division_id int
);
CREATE TABLE attendance (
id int PRIMARY KEY --!
, employee_id int NOT NULL REFERENCES employee -- FK!
, activity_type int
, created_at timestamptz NOT NULL
);
定义 PK 可以更容易地聚合行,因为 PK 覆盖 GROUP BY
子句中的整行。见:
我不会使用“名称”作为列名称。这不是描述性的。每隔一列可以命名为“名称”。考虑:
SELECT *
FROM ( -- complete employee/date grid for division in range
SELECT g.d::date AS the_date, id AS employee_id, name, division_id
FROM (
SELECT generate_series(MIN(created_at) AT TIME ZONE 'Asia/Kuala_Lumpur'
, MAX(created_at) AT TIME ZONE 'Asia/Kuala_Lumpur'
, interval '1 day')
FROM attendance
) g(d)
CROSS JOIN employee e
WHERE e.division_id = 1
) de
LEFT JOIN ( -- checkins & checkouts per employee/date for division in range
SELECT employee_id, ts::date AS the_date
, array_agg(id) as rows
, min(ts) FILTER (WHERE activity_type = 1) AS min_check_in
, max(ts) FILTER (WHERE activity_type = 2) AS max_check_out
, array_agg(ts::time) FILTER (WHERE activity_type = 1) AS check_ins
, array_agg(ts::time) FILTER (WHERE activity_type = 2) AS check_outs
FROM (
SELECT a.id, a.employee_id, a.activity_type, a.created_at AT TIME ZONE 'Asia/Kuala_Lumpur' AS ts -- convert to timestamp
FROM employee e
JOIN attendance a ON a.employee_id = e.id
-- WHERE a.created_at >= timestamp '2020-11-20' AT TIME ZONE 'Asia/Kuala_Lumpur' -- "sargable" expressions
-- AND a.created_at < timestamp '2020-11-21' AT TIME ZONE 'Asia/Kuala_Lumpur' -- exclusive upper bound (includes all of 2020-11-20);
AND e.division_id = 1
ORDER BY a.employee_id, a.created_at, a.activity_type -- optional to guarantee sorted arrays
) sub
GROUP BY 1, 2
) a USING (the_date, employee_id)
ORDER BY 1, 2;
db<> fiddle here
请注意,我的查询输出 Asia/Kuala_Lumpur 的本地日期和时间:
test=> SELECT timestamptz '2020-11-20 08:52:01 +0' AT TIME ZONE 'Asia/Kuala_Lumpur' AS local_ts;
local_ts
---------------------
2020-11-20 16:52:01
从哪里开始?您需要了解时区和 Postgres 数据类型的概念 timestamp with time zone
(timestamptz
) 与 timestamp without time区域
(时间戳
)。否则,将是无止境的困惑。从这里开始:
最值得注意的是,timestamptz
不存储时区:
当简单地将 timestamptz
转换为 date
或 timestamp
时,假定 session 的当前时区设置。 不是你想要的。使用 AT TIME ZONE
构造显式提供时区以避免此错误。在你的 fiddle 中你有两个:
...
, generate_series(
startdate::timestamp AT TIME ZONE 'Asia/Kuala_Lumpur',
enddate::timestamp AT TIME ZONE 'Asia/Kuala_Lumpur',
interval '1 day') g(d)
...
也不做你想做的事。在(错误!)转换为 timestamp
之后,AT TIME ZONE
构造将值转换回 timestamptz
。
此外,您的查询会生成所有用户的完整笛卡尔积,并在 attendance
表中生成最大天数范围,只是为了将其减少回单日:
where created_at >= timestamp '2020-11-20' AT TIME ZONE 'Asia/Kuala_Lumpur'
and created_at < timestamp '2020-11-20' AT TIME ZONE 'Asia/Kuala_Lumpur' + interval '1 day'
WHERE
子句最终完成了它应该做的事情。但是首先生成完整的天数范围,然后丢弃大部分天数是没有意义的。 (您似乎同时从 my other fiddle 复制了它?)
我注释掉了 WHERE
子句,并在我的查询中保留了您的 generate_series()
的优化版本作为概念证明。延伸阅读:
关于sql - 在带/不带时区的日期或时间戳的查询中处理 generate_series(),我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/65278993/
我正在尝试使用 SQLite CLI,但无法获得 generate_series功能来工作。我可以按照文档中的建议使用递归 CTE 对其进行模拟,但我似乎无法获得该链接中的任何示例。这是我的 sess
我正在尝试计算 PostgreSQL 中特定日期范围之间的工作日。 SELECT SUM(CASE WHEN extract (dow FROM foo) IN(1,2,3,4,5) THE
所以我试图跳过使用外部脚本生成模拟数据,而是在 PostgreSQL 中使用 generate_series()。如果我确实尝试减少行数,充其量它会返回“无法写入 block :临时日志文件.....
长话短说;我需要生成两个给定日期之间的所有日期。我的第一直觉就是 generate_series 函数,比如 \set start_date '''2019-10-01''' \set end_dat
我有一个按月出勤的 View (日期按月截断): ---------------------------------------------------- | Student_ID | Date
我正在使用这种语法: generate_series(1, COALESCE((string_to_array(table.id_number, '-')) [2] :: INT, 1)) AS n
这是我想在 Greenplum 数据库(如 postgresql 8.2.15)中完成的非常基本的查询。 表t中的字段create_date是timestamp w/o time zone。谁能指出我
我有一张混合了多边形和多边形的表格,我想运行一个函数将它们分解为 LineString(或 MultiLinestring)。 我的问题 - 我目前正在返回一个集合......没有几何(?).....
我有一个查询要根据按 date 和 employee_id 分组的日期系列生成报告。日期应基于特定时区,在本例中为“Asia/Kuala_Lumpur”。但这可能会根据用户所在的时区而改变。 SELE
当我运行SQL查询时: select generate_series(0,g) from ( select date(date1) - date(date2) as g from mytable
我想使用 Postgres 的 generate_series() 生成一系列时间戳,但它返回了错误数量的结果: select generate_series( no
下面的sql代码按预期运行 generate_series('2018-06-29 00:00:00','2018-06-29 23:00:00', interval '1 hour') 但是当我使用
我的应用程序遇到了一些问题。它适用于我的开发环境,但不适用于我的客户服务器。 带有时间戳的 generate_series 不起作用。相同的函数,但可以使用整数。 错误信息: [Request pr
generate_series 函数是 Postgres 中非常酷的功能之一,但我发现它令人困惑。如果您想要单个 系列数字、递增的时间戳等,这非常简单。但是当您想要用多列填充测试数据行时(对我而言)就
我知道 distinct 和 generate series 的意思。但是当我执行这个查询时,问号在我脑海中飞舞。 select distinct generate_series(0,8) 结果很奇怪
我通过 PostgreSQL 的 generate_series(min, max) 以下列方式生成日期系列: SELECT generate_series(getstartdate(some
我试图找到每月执行操作 A 或操作 B 的 # 个用户。 表:用户- ID- “创建日期” 表:action_A- user_id (= user.id)- “创建日期” 表:action_B- us
以下查询在 PostgreSQL 11.1 中需要超过 7 分钟: SELECT '2019-01-19' as date, '2019-01-19'::date - generate
查看日期范围时,generate_series 是否也返回开始日期和结束日期? select '2014-06-05 00:00:00'::timestamp + ('1 month'::IN
我有以下命令 select generate_series('2019-09-01'::timestamp, least(current_date, '2019-09-18'), interval '
我是一名优秀的程序员,十分优秀!