⭐️⭐️⭐️⭐️⭐️完整数据详见 练习数据·免费⭐️⭐️⭐️⭐️⭐️问题生成2012年8月31日凌晨1点的时间戳。预期结果timestamp2012-08-31 01:00:00[答案与讨论]select timestamp 2012-08-31 01:00:00;这个问题作为开篇相当简单SQL提供了多种日期和时间类型您可以在PostgreSQL官方文档中详细了解。这些类型基本上可以让您存储日期、时间或时间戳日期时间。在正常情况下标准答案是创建时间戳的最佳方式。您还可以使用类型转换将格式正确的字符串转换为时间戳例如select 2012-08-31 01:00:00::timestamp; select cast(2012-08-31 01:00:00 as timestamp);前一种方法是PostgreSQL的扩展功能而后一种符合SQL标准。您会注意到在我们之前的许多问题中我们使用了未指定数据类型的纯字符串。这是因为当PostgreSQL处理来自表中时间戳列的值时它知道将我们的字符串转换为时间戳。时间戳可以存储带或不带时区信息。这里我们选择不带时区但如果您愿意可以将时间戳格式化为2012-08-31 01:00:00 00:00假设使用UTC时间。请注意带时区的时间戳是与普通时间戳不同的类型——声明时应使用TIMESTAMP WITH TIME ZONE 2012-08-31 01:00:00 00:00。最后建议您尝试一下PostgreSQL文档中描述的各种日期/时间序列化格式。您会发现PostgreSQL对接受的格式非常灵活不过我的建议是使用我们这里使用的标准序列化方式——这种方式无歧义且易于移植到其他数据库。时间戳相减问题计算时间戳’2012-08-31 01:00:00’减去’2012-07-30 01:00:00’的结果。预期结果interval32 days[答案与讨论]select timestamp 2012-08-31 01:00:00 - timestamp 2012-07-30 01:00:00 as interval;时间戳相减会产生一个INTERVAL数据类型。INTERVAL是一种特殊的数据类型用于表示两个TIMESTAMP类型之间的差值。当对时间戳进行减法运算时PostgreSQL通常会以天、小时、分钟、秒的形式给出间隔而不会涉及月份。这通常会让事情变得更简单因为月份的长度是可变的。不过间隔的一个有用之处在于它们_可以_编码月份。假设我想安排某件事在恰好一个月后发生不管这个月有多少天。为此我可以使用[timestamp] interval ‘1 month’。间隔与SQL处理DATE类型的方式形成对比。日期不使用间隔——相反两个日期相减将返回一个整数表示两个日期之间的天数。您还可以向日期添加整数值。根据您对日期处理所需的智能程度这种方式有时会更加方便生成2012年10月所有日期的列表问题生成2012年10月所有日期的列表。可以输出为时间戳时间设为午夜或日期格式。预期结果ts2012-10-01 00:00:002012-10-02 00:00:002012-10-03 00:00:002012-10-04 00:00:002012-10-05 00:00:002012-10-06 00:00:002012-10-07 00:00:002012-10-08 00:00:002012-10-09 00:00:002012-10-10 00:00:002012-10-11 00:00:002012-10-12 00:00:002012-10-13 00:00:002012-10-14 00:00:002012-10-15 00:00:002012-10-16 00:00:002012-10-17 00:00:002012-10-18 00:00:002012-10-19 00:00:002012-10-20 00:00:002012-10-21 00:00:002012-10-22 00:00:002012-10-23 00:00:002012-10-24 00:00:002012-10-25 00:00:002012-10-26 00:00:002012-10-27 00:00:002012-10-28 00:00:002012-10-29 00:00:002012-10-30 00:00:002012-10-31 00:00:00[答案与讨论]select generate_series(timestamp 2012-10-01, timestamp 2012-10-31, interval 1 day) as ts;PostgreSQL相比其他数据库的一个最佳特性是一个名为GENERATE_SERIES的简单函数。此函数允许您生成日期或数字列表指定起始值、结束值和增量值。它在需要输出例如一个月内每天销售额的情况下非常有用。在包含销售列表的表上执行此操作的典型方法是使用SUM聚合按日期和产品类型分组。不幸的是这种方法有一个缺陷如果某天没有销售记录它就不会显示为了使其正常工作您需要从连续的时间戳列表左连接到聚合数据以填补空白。在其他数据库系统上保留一个充满日期的日历表来执行这些连接并不罕见。或者在某些系统上您可以使用递归CTE编写类似generate_series的功能。幸运的是PostgreSQL让我们的生活变得更加轻松从时间戳获取月份中的日期问题从时间戳’2012-08-31’中获取月份中的日期以整数形式返回。预期结果date_part31[答案与讨论]select extract(day from timestamp 2012-08-31);EXTRACT函数用于获取时间戳或间隔的各个部分。您可以将时间戳中任何字段的值作为整数获取。计算时间戳之间的秒数问题计算时间戳’2012-08-31 01:00:00’和’2012-09-02 00:00:00’之间的秒数。预期结果date_part169200[答案与讨论]select extract(epoch from (timestamp 2012-09-02 00:00:00 - 2012-08-31 01:00:00));上述答案是PostgreSQL特有的技巧。提取epoch会将间隔或时间戳分别转换为秒数或者转换为自epoch1970年1月1日以来的秒数。如果您想要分钟、小时等数量只需适当除以相应的秒数即可。如果您想编写更具可移植性的代码不幸的是您会发现不能使用extract epoch。相反您需要使用类似以下的方法select extract(day from ts.int)*60*60*24 extract(hour from ts.int)*60*60 extract(minute from ts.int)*60 extract(second from ts.int) from (select timestamp 2012-09-02 00:00:00 - 2012-08-31 01:00:00 as int) ts如您所见这种方式相当糟糕。如果您计划编写跨平台SQL我建议为每个数据库管理系统建立一个常用用户定义函数库以便规范化此类常见需求。这样可以使您的主代码库更加清晰。计算2012年每个月的天数问题对于2012年的每个月输出该月的天数。将输出格式化为一个包含月份编号的整数列以及一个包含间隔数据类型的第二列。预期结果monthlength131 days229 days331 days430 days531 days630 days731 days831 days930 days1031 days1130 days1231 days[答案与讨论]select extract(month from cal.month) as month, (cal.month interval 1 month) - cal.month as length from ( select generate_series(timestamp 2012-01-01, timestamp 2012-12-01, interval 1 month) as month ) cal order by month;这个答案展示了我们学到的几个概念。我们使用GENERATE_SERIES函数生成一年的时间戳每次递增一个月。然后我们使用EXTRACT函数获取月份编号。最后我们将每个时间戳加1个月后减去自身。值得注意的是两个时间戳相减总是会产生以天或天的部分为单位的间隔。您不会得到以月或年为单位的简单答案因为这些时间段的长度是可变的。计算月份剩余天数问题对于任何给定的时间戳计算该月剩余的天数。当前日期应算作完整的一天不考虑具体时间。使用’2012-02-11 01:00:00’作为示例时间戳来制作答案。将输出格式化为单个间隔值。预期结果remaining19 days[答案与讨论]select (date_trunc(month,ts.testts) interval 1 month) - date_trunc(day, ts.testts) as remaining from (select timestamp 2012-02-11 01:00:00 as testts) ts本题的主角是DATE_TRUNC函数。它的功能正如您所期望的——将日期截断到指定的分钟、小时、日、月等。我们解决这个问题的方法是截断时间戳以找到所在的月份然后加上一个月再减去我们的时间戳。为了确保部分日期被视为完整的天数我们减去的时间戳被截断到最近的天。注意我们将时间戳放入子查询的方式。这不是必需的但这样做您可以给时间戳一个名称而不必重复列出字面值。计算预订的结束时间问题返回系统中最后10个预订的开始和结束时间列表按结束时间排序然后按开始时间排序。预期结果starttimeendtime2013-01-01 15:30:002013-01-01 16:00:002012-09-30 19:30:002012-09-30 20:30:002012-09-30 19:00:002012-09-30 20:30:002012-09-30 19:30:002012-09-30 20:00:002012-09-30 19:00:002012-09-30 20:00:002012-09-30 19:00:002012-09-30 20:00:002012-09-30 18:30:002012-09-30 20:00:002012-09-30 18:30:002012-09-30 20:00:002012-09-30 19:00:002012-09-30 19:30:002012-09-30 18:30:002012-09-30 19:30:00[答案与讨论]select starttime, starttime slots*(interval 30 minutes) endtime from cd.bookings order by endtime desc, starttime desc limit 10这个问题简单地返回预订的开始时间以及计算得出的结束时间等于开始时间 (30分钟 × 时段数)。请注意完全可以对间隔进行乘法运算。您还会注意到使用ORDER BY和LIMIT来获取最后十个预订。这只是按结束时间的降序排列预订并选取前十个。返回每个月的预订数量问题返回每个月的预订数量按月份排序。预期结果monthcount2012-07-01 00:00:006582012-08-01 00:00:0014722012-09-01 00:00:0019132013-01-01 00:00:001[答案与讨论]select date_trunc(month, starttime) as month, count(*) from cd.bookings group by month order by month这个问题相当简单复用了我们之前见过的概念。我们只需计算预订数量并按预订开始时间截断到月份进行聚合。按月份计算每个设施的利用率百分比问题按月份计算每个设施的利用率百分比按名称和月份排序四舍五入到小数点后1位。开放时间为早上8点关闭时间为晚上8点半。您可以将每个月视为完整月份无论俱乐部在某些日期是否开放。预期结果namemonthutilisationBadminton Court2012-07-01 00:00:0023.2Badminton Court2012-08-01 00:00:0059.2Badminton Court2012-09-01 00:00:0076.0Massage Room 12012-07-01 00:00:0034.1Massage Room 12012-08-01 00:00:0063.5Massage Room 12012-09-01 00:00:0086.4Massage Room 22012-07-01 00:00:003.1Massage Room 22012-08-01 00:00:0010.6Massage Room 22012-09-01 00:00:0016.3Pool Table2012-07-01 00:00:0015.1Pool Table2012-08-01 00:00:0041.5Pool Table2012-09-01 00:00:0062.8Pool Table2013-01-01 00:00:000.1Snooker Table2012-07-01 00:00:0020.1Snooker Table2012-08-01 00:00:0042.1Snooker Table2012-09-01 00:00:0056.8Squash Court2012-07-01 00:00:0021.2Squash Court2012-08-01 00:00:0051.6Squash Court2012-09-01 00:00:0072.0Table Tennis2012-07-01 00:00:0013.4Table Tennis2012-08-01 00:00:0039.2Table Tennis2012-09-01 00:00:0056.3Tennis Court 12012-07-01 00:00:0034.8Tennis Court 12012-08-01 00:00:0059.2Tennis Court 12012-09-01 00:00:0078.8Tennis Court 22012-07-01 00:00:0026.7Tennis Court 22012-08-01 00:00:0062.3Tennis Court 22012-09-01 00:00:0078.4[答案与讨论]select name, month, round((100*slots)/ cast( 25*(cast((month interval 1 month) as date) - cast (month as date)) as numeric),1) as utilisation from ( select facs.name as name, date_trunc(month, starttime) as month, sum(slots) as slots from cd.bookings bks inner join cd.facilities facs on bks.facid facs.facid group by facs.facid, month ) as inn order by name, month这个查询的核心部分内部子查询其实相当简单通过聚合计算每个设施每月的总使用时段数。如果您已经学习了本节其余部分和聚合类别的内容这部分对您来说可能不会太具挑战性。不幸的是这个查询确实存在一些其他复杂性计算每个月的天数。我们可以通过减去两个相隔一个月的时间戳来计算两个月之间的天数。不幸的是这会返回一个间隔数据类型我们无法用它进行数学运算。在这种情况下我们通过先将时间戳转换为_日期_再进行减法来绕过这个限制。对日期类型进行减法会得到整数天数。这种变通方法的替代方案是将间隔转换为_epoch_值即秒数。为此使用EXTRACT(EPOCH FROM month)/(246060)。可以说这是一种更好的处理方式但在其他数据库系统上的可移植性较差。
Postgresql基础实践教程（六）
发布时间：2026/5/25 1:55:42
⭐️⭐️⭐️⭐️⭐️完整数据详见 练习数据·免费⭐️⭐️⭐️⭐️⭐️问题生成2012年8月31日凌晨1点的时间戳。预期结果timestamp2012-08-31 01:00:00[答案与讨论]select timestamp 2012-08-31 01:00:00;这个问题作为开篇相当简单SQL提供了多种日期和时间类型您可以在PostgreSQL官方文档中详细了解。这些类型基本上可以让您存储日期、时间或时间戳日期时间。在正常情况下标准答案是创建时间戳的最佳方式。您还可以使用类型转换将格式正确的字符串转换为时间戳例如select 2012-08-31 01:00:00::timestamp; select cast(2012-08-31 01:00:00 as timestamp);前一种方法是PostgreSQL的扩展功能而后一种符合SQL标准。您会注意到在我们之前的许多问题中我们使用了未指定数据类型的纯字符串。这是因为当PostgreSQL处理来自表中时间戳列的值时它知道将我们的字符串转换为时间戳。时间戳可以存储带或不带时区信息。这里我们选择不带时区但如果您愿意可以将时间戳格式化为2012-08-31 01:00:00 00:00假设使用UTC时间。请注意带时区的时间戳是与普通时间戳不同的类型——声明时应使用TIMESTAMP WITH TIME ZONE 2012-08-31 01:00:00 00:00。最后建议您尝试一下PostgreSQL文档中描述的各种日期/时间序列化格式。您会发现PostgreSQL对接受的格式非常灵活不过我的建议是使用我们这里使用的标准序列化方式——这种方式无歧义且易于移植到其他数据库。时间戳相减问题计算时间戳’2012-08-31 01:00:00’减去’2012-07-30 01:00:00’的结果。预期结果interval32 days[答案与讨论]select timestamp 2012-08-31 01:00:00 - timestamp 2012-07-30 01:00:00 as interval;时间戳相减会产生一个INTERVAL数据类型。INTERVAL是一种特殊的数据类型用于表示两个TIMESTAMP类型之间的差值。当对时间戳进行减法运算时PostgreSQL通常会以天、小时、分钟、秒的形式给出间隔而不会涉及月份。这通常会让事情变得更简单因为月份的长度是可变的。不过间隔的一个有用之处在于它们_可以_编码月份。假设我想安排某件事在恰好一个月后发生不管这个月有多少天。为此我可以使用[timestamp] interval ‘1 month’。间隔与SQL处理DATE类型的方式形成对比。日期不使用间隔——相反两个日期相减将返回一个整数表示两个日期之间的天数。您还可以向日期添加整数值。根据您对日期处理所需的智能程度这种方式有时会更加方便生成2012年10月所有日期的列表问题生成2012年10月所有日期的列表。可以输出为时间戳时间设为午夜或日期格式。预期结果ts2012-10-01 00:00:002012-10-02 00:00:002012-10-03 00:00:002012-10-04 00:00:002012-10-05 00:00:002012-10-06 00:00:002012-10-07 00:00:002012-10-08 00:00:002012-10-09 00:00:002012-10-10 00:00:002012-10-11 00:00:002012-10-12 00:00:002012-10-13 00:00:002012-10-14 00:00:002012-10-15 00:00:002012-10-16 00:00:002012-10-17 00:00:002012-10-18 00:00:002012-10-19 00:00:002012-10-20 00:00:002012-10-21 00:00:002012-10-22 00:00:002012-10-23 00:00:002012-10-24 00:00:002012-10-25 00:00:002012-10-26 00:00:002012-10-27 00:00:002012-10-28 00:00:002012-10-29 00:00:002012-10-30 00:00:002012-10-31 00:00:00[答案与讨论]select generate_series(timestamp 2012-10-01, timestamp 2012-10-31, interval 1 day) as ts;PostgreSQL相比其他数据库的一个最佳特性是一个名为GENERATE_SERIES的简单函数。此函数允许您生成日期或数字列表指定起始值、结束值和增量值。它在需要输出例如一个月内每天销售额的情况下非常有用。在包含销售列表的表上执行此操作的典型方法是使用SUM聚合按日期和产品类型分组。不幸的是这种方法有一个缺陷如果某天没有销售记录它就不会显示为了使其正常工作您需要从连续的时间戳列表左连接到聚合数据以填补空白。在其他数据库系统上保留一个充满日期的日历表来执行这些连接并不罕见。或者在某些系统上您可以使用递归CTE编写类似generate_series的功能。幸运的是PostgreSQL让我们的生活变得更加轻松从时间戳获取月份中的日期问题从时间戳’2012-08-31’中获取月份中的日期以整数形式返回。预期结果date_part31[答案与讨论]select extract(day from timestamp 2012-08-31);EXTRACT函数用于获取时间戳或间隔的各个部分。您可以将时间戳中任何字段的值作为整数获取。计算时间戳之间的秒数问题计算时间戳’2012-08-31 01:00:00’和’2012-09-02 00:00:00’之间的秒数。预期结果date_part169200[答案与讨论]select extract(epoch from (timestamp 2012-09-02 00:00:00 - 2012-08-31 01:00:00));上述答案是PostgreSQL特有的技巧。提取epoch会将间隔或时间戳分别转换为秒数或者转换为自epoch1970年1月1日以来的秒数。如果您想要分钟、小时等数量只需适当除以相应的秒数即可。如果您想编写更具可移植性的代码不幸的是您会发现不能使用extract epoch。相反您需要使用类似以下的方法select extract(day from ts.int)*60*60*24 extract(hour from ts.int)*60*60 extract(minute from ts.int)*60 extract(second from ts.int) from (select timestamp 2012-09-02 00:00:00 - 2012-08-31 01:00:00 as int) ts如您所见这种方式相当糟糕。如果您计划编写跨平台SQL我建议为每个数据库管理系统建立一个常用用户定义函数库以便规范化此类常见需求。这样可以使您的主代码库更加清晰。计算2012年每个月的天数问题对于2012年的每个月输出该月的天数。将输出格式化为一个包含月份编号的整数列以及一个包含间隔数据类型的第二列。预期结果monthlength131 days229 days331 days430 days531 days630 days731 days831 days930 days1031 days1130 days1231 days[答案与讨论]select extract(month from cal.month) as month, (cal.month interval 1 month) - cal.month as length from ( select generate_series(timestamp 2012-01-01, timestamp 2012-12-01, interval 1 month) as month ) cal order by month;这个答案展示了我们学到的几个概念。我们使用GENERATE_SERIES函数生成一年的时间戳每次递增一个月。然后我们使用EXTRACT函数获取月份编号。最后我们将每个时间戳加1个月后减去自身。值得注意的是两个时间戳相减总是会产生以天或天的部分为单位的间隔。您不会得到以月或年为单位的简单答案因为这些时间段的长度是可变的。计算月份剩余天数问题对于任何给定的时间戳计算该月剩余的天数。当前日期应算作完整的一天不考虑具体时间。使用’2012-02-11 01:00:00’作为示例时间戳来制作答案。将输出格式化为单个间隔值。预期结果remaining19 days[答案与讨论]select (date_trunc(month,ts.testts) interval 1 month) - date_trunc(day, ts.testts) as remaining from (select timestamp 2012-02-11 01:00:00 as testts) ts本题的主角是DATE_TRUNC函数。它的功能正如您所期望的——将日期截断到指定的分钟、小时、日、月等。我们解决这个问题的方法是截断时间戳以找到所在的月份然后加上一个月再减去我们的时间戳。为了确保部分日期被视为完整的天数我们减去的时间戳被截断到最近的天。注意我们将时间戳放入子查询的方式。这不是必需的但这样做您可以给时间戳一个名称而不必重复列出字面值。计算预订的结束时间问题返回系统中最后10个预订的开始和结束时间列表按结束时间排序然后按开始时间排序。预期结果starttimeendtime2013-01-01 15:30:002013-01-01 16:00:002012-09-30 19:30:002012-09-30 20:30:002012-09-30 19:00:002012-09-30 20:30:002012-09-30 19:30:002012-09-30 20:00:002012-09-30 19:00:002012-09-30 20:00:002012-09-30 19:00:002012-09-30 20:00:002012-09-30 18:30:002012-09-30 20:00:002012-09-30 18:30:002012-09-30 20:00:002012-09-30 19:00:002012-09-30 19:30:002012-09-30 18:30:002012-09-30 19:30:00[答案与讨论]select starttime, starttime slots*(interval 30 minutes) endtime from cd.bookings order by endtime desc, starttime desc limit 10这个问题简单地返回预订的开始时间以及计算得出的结束时间等于开始时间 (30分钟 × 时段数)。请注意完全可以对间隔进行乘法运算。您还会注意到使用ORDER BY和LIMIT来获取最后十个预订。这只是按结束时间的降序排列预订并选取前十个。返回每个月的预订数量问题返回每个月的预订数量按月份排序。预期结果monthcount2012-07-01 00:00:006582012-08-01 00:00:0014722012-09-01 00:00:0019132013-01-01 00:00:001[答案与讨论]select date_trunc(month, starttime) as month, count(*) from cd.bookings group by month order by month这个问题相当简单复用了我们之前见过的概念。我们只需计算预订数量并按预订开始时间截断到月份进行聚合。按月份计算每个设施的利用率百分比问题按月份计算每个设施的利用率百分比按名称和月份排序四舍五入到小数点后1位。开放时间为早上8点关闭时间为晚上8点半。您可以将每个月视为完整月份无论俱乐部在某些日期是否开放。预期结果namemonthutilisationBadminton Court2012-07-01 00:00:0023.2Badminton Court2012-08-01 00:00:0059.2Badminton Court2012-09-01 00:00:0076.0Massage Room 12012-07-01 00:00:0034.1Massage Room 12012-08-01 00:00:0063.5Massage Room 12012-09-01 00:00:0086.4Massage Room 22012-07-01 00:00:003.1Massage Room 22012-08-01 00:00:0010.6Massage Room 22012-09-01 00:00:0016.3Pool Table2012-07-01 00:00:0015.1Pool Table2012-08-01 00:00:0041.5Pool Table2012-09-01 00:00:0062.8Pool Table2013-01-01 00:00:000.1Snooker Table2012-07-01 00:00:0020.1Snooker Table2012-08-01 00:00:0042.1Snooker Table2012-09-01 00:00:0056.8Squash Court2012-07-01 00:00:0021.2Squash Court2012-08-01 00:00:0051.6Squash Court2012-09-01 00:00:0072.0Table Tennis2012-07-01 00:00:0013.4Table Tennis2012-08-01 00:00:0039.2Table Tennis2012-09-01 00:00:0056.3Tennis Court 12012-07-01 00:00:0034.8Tennis Court 12012-08-01 00:00:0059.2Tennis Court 12012-09-01 00:00:0078.8Tennis Court 22012-07-01 00:00:0026.7Tennis Court 22012-08-01 00:00:0062.3Tennis Court 22012-09-01 00:00:0078.4[答案与讨论]select name, month, round((100*slots)/ cast( 25*(cast((month interval 1 month) as date) - cast (month as date)) as numeric),1) as utilisation from ( select facs.name as name, date_trunc(month, starttime) as month, sum(slots) as slots from cd.bookings bks inner join cd.facilities facs on bks.facid facs.facid group by facs.facid, month ) as inn order by name, month这个查询的核心部分内部子查询其实相当简单通过聚合计算每个设施每月的总使用时段数。如果您已经学习了本节其余部分和聚合类别的内容这部分对您来说可能不会太具挑战性。不幸的是这个查询确实存在一些其他复杂性计算每个月的天数。我们可以通过减去两个相隔一个月的时间戳来计算两个月之间的天数。不幸的是这会返回一个间隔数据类型我们无法用它进行数学运算。在这种情况下我们通过先将时间戳转换为_日期_再进行减法来绕过这个限制。对日期类型进行减法会得到整数天数。这种变通方法的替代方案是将间隔转换为_epoch_值即秒数。为此使用EXTRACT(EPOCH FROM month)/(246060)。可以说这是一种更好的处理方式但在其他数据库系统上的可移植性较差。
