Thread: Output of date_part('quarter', date)

Hello,
I tried to use function date_part('quarter', date) in query and I have
got wrong results - for first quarter I've got all rows from 01.01 to
31.03
for second - from from 01.04 to 31.07 (!!!)
for third  - from from 01.08 to 30.11 (!!!)
for forth  - from from 01.12 to 31.12 (!!!)
-----------------------
PostgreSQL 7.0.2 on i386-pc-bsdi4.0.1, compiled by gcc 2.7.2.1
-----------------------
Queries:

billing=> select InDate from FirmICO where date_part('year', indate)=2000 and date_part('quarter', indate)=1 ORDER BY
InDate;
         indate
------------------------
 2000-01-06 16:05:04+06
 2000-01-06 16:06:50+06
 2000-01-10 12:19:34+06
 2000-01-10 12:20:07+06
 2000-01-11 11:18:08+06
 2000-01-11 14:41:40+06
 2000-01-12 10:04:05+06
 2000-01-12 15:33:07+06
 2000-01-13 11:32:11+06
 2000-01-14 10:27:57+06
 2000-01-17 13:48:44+06
 2000-01-17 15:39:30+06
 2000-01-18 17:35:10+06
 2000-01-18 18:04:14+06
 2000-01-19 11:08:55+06
 2000-01-19 11:09:29+06
 2000-01-20 10:13:34+06
 2000-01-20 12:28:59+06
 2000-01-21 10:43:20+06
 2000-01-24 15:16:09+06
 2000-01-25 11:26:51+06
 2000-01-26 08:56:56+06
 2000-01-26 12:07:17+06
 2000-01-27 11:07:26+06
 2000-01-27 14:18:51+06
 2000-01-28 11:02:30+06
 2000-01-28 17:08:50+06
 2000-01-29 17:41:54+06
 2000-01-31 10:13:31+06
 2000-01-31 16:36:38+06
 2000-01-31 16:39:16+06
 2000-02-01 11:39:13+06
 2000-02-02 10:34:28+06
 2000-02-03 11:40:45+06
 2000-02-04 10:52:18+06
 2000-02-07 10:48:39+06
 2000-02-07 16:36:19+06
 2000-02-07 16:55:50+06
 2000-02-07 16:57:45+06
 2000-02-08 10:31:06+06
 2000-02-09 11:52:13+06
 2000-02-10 10:57:24+06
 2000-02-10 13:21:28+06
 2000-02-11 11:36:22+06
 2000-02-14 12:27:07+06
 2000-02-15 10:57:01+06
 2000-02-15 10:57:40+06
 2000-02-15 17:38:40+06
 2000-02-16 12:00:27+06
 2000-02-17 10:47:41+06
 2000-02-17 14:23:55+06
 2000-02-18 10:56:09+06
 2000-02-21 10:49:42+06
 2000-02-22 11:03:45+06
 2000-02-22 17:39:58+06
 2000-02-23 10:49:47+06
 2000-02-23 10:51:13+06
 2000-02-24 12:49:57+06
 2000-02-25 10:36:28+06
 2000-02-28 11:45:46+06
 2000-02-29 09:43:17+06
 2000-02-29 11:04:24+06
 2000-02-29 11:12:53+06
 2000-02-29 17:39:29+06
 2000-03-02 11:06:36+06
 2000-03-02 13:19:07+06
 2000-03-03 13:09:31+06
 2000-03-06 10:56:27+06
 2000-03-06 16:29:30+06
 2000-03-07 12:04:32+06
 2000-03-07 17:02:46+06
 2000-03-09 10:53:37+06
 2000-03-09 11:29:59+06
 2000-03-10 10:35:56+06
 2000-03-13 10:50:45+06
 2000-03-14 17:34:12+06
 2000-03-14 17:36:11+06
 2000-03-15 11:46:16+06
 2000-03-16 11:41:51+06
 2000-03-17 11:19:46+06
 2000-03-20 10:00:12+06
 2000-03-21 10:23:10+06
 2000-03-22 10:10:34+06
 2000-03-23 11:27:53+06
 2000-03-23 15:37:24+06
 2000-03-24 11:07:02+06
 2000-03-24 17:32:22+06
 2000-03-25 16:04:41+06
 2000-03-27 12:06:45+07
 2000-03-28 10:17:26+07
 2000-03-29 11:53:05+07
 2000-03-29 12:16:18+07
 2000-03-30 11:45:04+07
 2000-03-31 12:37:34+07
 2000-03-31 17:51:58+07
 2000-03-31 19:03:39+07
(96 rows)

billing=> select InDate from FirmICO where date_part('year', indate)=2000 and date_part('quarter', indate)=2 ORDER BY
InDate;
         indate
------------------------
 2000-04-03 11:06:56+07
 2000-04-04 13:30:11+07
 2000-04-05 11:58:54+07
 2000-04-05 13:09:53+07
 2000-04-05 14:36:16+07
 2000-04-05 17:45:07+07
 2000-04-06 11:03:51+07
 2000-04-07 10:47:30+07
 2000-04-10 11:26:41+07
 2000-04-11 13:01:13+07
 2000-04-12 15:42:38+07
 2000-04-12 15:44:08+07
 2000-04-13 16:59:37+07
 2000-04-14 11:00:21+07
 2000-04-14 17:37:51+07
 2000-04-17 13:23:31+07
 2000-04-18 12:50:59+07
 2000-04-18 12:51:48+07
 2000-04-19 11:24:10+07
 2000-04-20 10:35:03+07
 2000-04-20 16:10:58+07
 2000-04-21 11:28:44+07
 2000-04-21 11:29:12+07
 2000-04-24 11:08:42+07
 2000-04-25 11:35:01+07
 2000-04-26 11:22:12+07
 2000-04-26 17:30:06+07
 2000-04-27 11:36:57+07
 2000-04-28 11:28:44+07
 2000-04-28 18:29:45+07
 2000-04-28 19:11:10+07
 2000-04-29 16:00:08+07
 2000-05-04 10:12:29+07
 2000-05-05 11:41:29+07
 2000-05-05 16:29:43+07
 2000-05-06 16:29:14+07
 2000-05-06 16:30:10+07
 2000-05-10 17:55:20+07
 2000-05-11 10:36:16+07
 2000-05-11 11:59:54+07
 2000-05-12 15:38:40+07
 2000-05-15 10:54:19+07
 2000-05-15 12:58:14+07
 2000-05-16 11:22:38+07
 2000-05-16 14:43:42+07
 2000-05-17 12:02:38+07
 2000-05-17 14:22:53+07
 2000-05-17 14:26:46+07
 2000-05-17 17:32:37+07
 2000-05-18 11:00:01+07
 2000-05-19 10:44:44+07
 2000-05-22 11:27:16+07
 2000-05-23 11:37:59+07
 2000-05-24 11:15:54+07
 2000-05-24 17:37:25+07
 2000-05-25 11:32:16+07
 2000-05-26 10:53:36+07
 2000-05-29 12:16:38+07
 2000-05-30 10:02:51+07
 2000-05-31 10:46:53+07
 2000-05-31 17:13:29+07
 2000-05-31 17:42:50+07
 2000-05-31 18:24:12+07
 2000-06-02 12:55:58+07
 2000-06-05 15:57:18+07
 2000-06-06 12:16:40+07
 2000-06-07 09:39:52+07
 2000-06-07 15:31:01+07
 2000-06-07 18:06:51+07
 2000-06-08 11:06:16+07
 2000-06-08 15:28:32+07
 2000-06-09 10:26:15+07
 2000-06-13 12:05:38+07
 2000-06-14 11:02:55+07
 2000-06-14 11:16:29+07
 2000-06-14 11:31:46+07
 2000-06-14 17:51:11+07
 2000-06-15 10:46:36+07
 2000-06-15 11:06:34+07
 2000-06-16 11:40:32+07
 2000-06-16 11:41:36+07
 2000-06-19 10:24:44+07
 2000-06-19 11:42:32+07
 2000-06-20 10:46:43+07
 2000-06-20 14:59:19+07
 2000-06-21 12:24:37+07
 2000-06-21 12:25:31+07
 2000-06-21 17:46:08+07
 2000-06-22 12:49:26+07
 2000-06-22 12:50:17+07
 2000-06-23 09:40:32+07
 2000-06-23 10:43:26+07
 2000-06-26 10:31:36+07
 2000-06-26 14:32:00+07
 2000-06-27 13:43:10+07
 2000-06-28 09:50:51+07
 2000-06-28 10:54:30+07
 2000-06-28 17:42:21+07
 2000-06-29 11:24:12+07
 2000-06-29 13:14:32+07
 2000-06-30 10:04:07+07
 2000-06-30 13:37:27+07
 2000-06-30 18:43:40+07
 2000-06-30 18:44:34+07
 2000-06-30 19:42:00+07
 2000-07-04 11:01:56+07
 2000-07-04 14:20:04+07
 2000-07-05 16:23:21+07
 2000-07-05 16:26:06+07
 2000-07-06 09:21:24+07
 2000-07-06 12:12:43+07
 2000-07-06 14:30:18+07
 2000-07-07 10:39:02+07
 2000-07-07 14:11:37+07
 2000-07-07 17:47:07+07
 2000-07-10 10:56:33+07
 2000-07-10 12:41:49+07
 2000-07-11 09:19:27+07
 2000-07-11 13:05:47+07
 2000-07-12 10:34:13+07
 2000-07-12 11:03:50+07
 2000-07-13 10:49:06+07
 2000-07-13 11:01:40+07
 2000-07-13 14:53:14+07
 2000-07-14 10:05:01+07
 2000-07-14 10:49:57+07
 2000-07-14 17:35:07+07
 2000-07-17 11:51:19+07
 2000-07-18 10:06:07+07
 2000-07-18 10:32:48+07
 2000-07-19 10:10:05+07
 2000-07-19 10:31:41+07
 2000-07-20 10:02:41+07
 2000-07-20 10:54:22+07
 2000-07-21 10:44:04+07
 2000-07-21 17:37:35+07
 2000-07-24 10:04:32+07
 2000-07-24 12:02:47+07
 2000-07-25 10:30:27+07
 2000-07-25 19:08:59+07
 2000-07-26 11:02:11+07
 2000-07-26 19:07:42+07
 2000-07-27 10:45:36+07
 2000-07-27 14:36:00+07
 2000-07-28 11:04:37+07
 2000-07-28 13:28:26+07
 2000-07-28 13:45:16+07
 2000-07-28 13:46:24+07
 2000-07-28 13:47:22+07
 2000-07-28 17:42:16+07
 2000-07-31 10:13:48+07
 2000-07-31 11:10:46+07
 2000-07-31 11:54:49+07
 2000-07-31 17:46:00+07
(154 rows)

billing=> select InDate from FirmICO where date_part('year', indate)=2000 and date_part('quarter', indate)=3 ORDER BY
InDate;
         indate
------------------------
 2000-08-01 17:35:11+07
 2000-08-02 10:51:29+07
 2000-08-03 14:05:53+07
 2000-08-03 14:06:45+07
 2000-08-04 10:30:24+07
 2000-08-04 17:34:20+07
 2000-08-04 18:54:48+07
 2000-08-07 12:25:53+07
 2000-08-07 12:26:49+07
 2000-08-07 17:28:30+07
 2000-08-08 10:45:10+07
 2000-08-08 10:45:26+07
 2000-08-09 11:04:12+07
 2000-08-09 11:31:07+07
 2000-08-09 18:10:22+07
 2000-08-10 10:16:05+07
 2000-08-10 10:43:27+07
 2000-08-11 10:08:29+07
 2000-08-11 12:23:28+07
 2000-08-14 12:24:24+07
 2000-08-14 12:24:51+07
 2000-08-15 10:47:17+07
 2000-08-15 17:42:46+07
 2000-08-16 13:13:40+07
 2000-08-16 17:35:48+07
 2000-08-17 11:11:58+07
 2000-08-17 11:21:47+07
 2000-08-18 10:12:38+07
 2000-08-18 10:44:27+07
 2000-08-18 10:45:08+07
 2000-08-21 10:54:00+07
 2000-08-21 10:54:31+07
 2000-08-22 11:05:22+07
 2000-08-22 11:34:49+07
 2000-08-23 10:58:42+07
 2000-08-23 13:39:59+07
 2000-08-23 16:40:22+07
 2000-08-23 16:41:05+07
 2000-08-23 17:35:56+07
 2000-08-23 17:46:16+07
 2000-08-24 11:01:06+07
 2000-08-25 10:12:28+07
 2000-08-25 10:36:10+07
 2000-08-28 11:31:35+07
 2000-08-28 11:55:30+07
 2000-08-29 11:01:16+07
 2000-08-29 11:29:12+07
 2000-08-30 11:55:27+07
 2000-08-30 11:56:08+07
 2000-08-31 10:55:26+07
 2000-08-31 17:11:37+07
 2000-08-31 17:12:53+07
 2000-08-31 17:46:42+07
 2000-08-31 17:48:23+07
 2000-09-02 00:00:00+07
 2000-09-04 00:00:00+07
 2000-09-04 00:00:00+07
 2000-09-05 00:00:00+07
 2000-09-05 00:00:00+07
 2000-09-05 00:00:00+07
 2000-09-05 00:00:00+07
 2000-09-06 00:00:00+07
 2000-09-06 00:00:00+07
 2000-09-06 00:00:00+07
 2000-09-07 00:00:00+07
 2000-09-08 00:00:00+07
 2000-09-08 00:00:00+07
 2000-09-11 00:00:00+07
 2000-09-11 00:00:00+07
 2000-09-12 00:00:00+07
 2000-09-12 00:00:00+07
 2000-09-13 00:00:00+07
 2000-09-13 00:00:00+07
 2000-09-13 00:00:00+07
 2000-09-14 00:00:00+07
 2000-09-14 00:00:00+07
 2000-09-15 00:00:00+07
 2000-09-15 00:00:00+07
 2000-09-18 00:00:00+07
 2000-09-18 00:00:00+07
 2000-09-19 00:00:00+07
 2000-09-19 00:00:00+07
 2000-09-20 00:00:00+07
 2000-09-20 00:00:00+07
 2000-09-21 00:00:00+07
 2000-09-22 00:00:00+07
 2000-09-22 00:00:00+07
 2000-09-22 00:00:00+07
 2000-09-25 00:00:00+07
 2000-09-25 00:00:00+07
 2000-09-26 00:00:00+07
 2000-09-26 00:00:00+07
 2000-09-27 00:00:00+07
 2000-09-27 00:00:00+07
 2000-09-28 00:00:00+07
 2000-09-28 00:00:00+07
 2000-09-28 00:00:00+07
 2000-09-29 00:00:00+07
 2000-09-29 00:00:00+07
 2000-09-30 00:00:00+07
 2000-09-30 00:00:00+07
 2000-09-30 00:00:00+07
 2000-10-03 00:00:00+07
 2000-10-03 00:00:00+07
 2000-10-04 00:00:00+07
 2000-10-04 00:00:00+07
 2000-10-05 00:00:00+07
 2000-10-05 00:00:00+07
 2000-10-06 00:00:00+07
 2000-10-06 00:00:00+07
 2000-10-06 00:00:00+07
 2000-10-09 00:00:00+07
 2000-10-09 00:00:00+07
 2000-10-09 00:00:00+07
 2000-10-10 00:00:00+07
 2000-10-10 00:00:00+07
 2000-10-11 00:00:00+07
 2000-10-11 00:00:00+07
 2000-10-12 00:00:00+07
 2000-10-12 00:00:00+07
 2000-10-12 00:00:00+07
 2000-10-13 00:00:00+07
 2000-10-13 00:00:00+07
 2000-10-16 00:00:00+07
 2000-10-16 00:00:00+07
 2000-10-17 00:00:00+07
 2000-10-17 00:00:00+07
 2000-10-18 00:00:00+07
 2000-10-18 00:00:00+07
 2000-10-18 00:00:00+07
 2000-10-19 00:00:00+07
 2000-10-19 00:00:00+07
 2000-10-19 00:00:00+07
 2000-10-20 00:00:00+07
 2000-10-20 00:00:00+07
 2000-10-23 00:00:00+07
 2000-10-23 00:00:00+07
 2000-10-24 00:00:00+07
 2000-10-24 00:00:00+07
 2000-10-25 00:00:00+07
 2000-10-25 00:00:00+07
 2000-10-26 00:00:00+07
 2000-10-26 00:00:00+07
 2000-10-26 00:00:00+07
 2000-10-27 00:00:00+07
 2000-10-27 00:00:00+07
 2000-10-30 00:00:00+06
 2000-10-30 00:00:00+06
 2000-10-31 00:00:00+06
 2000-10-31 00:00:00+06
 2000-10-31 00:00:00+06
 2000-10-31 00:00:00+06
 2000-10-31 00:00:00+06
 2000-11-02 00:00:00+06
 2000-11-02 00:00:00+06
 2000-11-03 00:00:00+06
 2000-11-03 00:00:00+06
 2000-11-03 00:00:00+06
 2000-11-04 00:00:00+06
 2000-11-04 00:00:00+06
 2000-11-04 00:00:00+06
 2000-11-04 00:00:00+06
 2000-11-06 00:00:00+06
 2000-11-08 00:00:00+06
 2000-11-09 00:00:00+06
 2000-11-09 00:00:00+06
 2000-11-10 00:00:00+06
 2000-11-10 00:00:00+06
 2000-11-10 00:00:00+06
 2000-11-13 00:00:00+06
 2000-11-14 00:00:00+06
 2000-11-14 00:00:00+06
 2000-11-15 00:00:00+06
 2000-11-15 00:00:00+06
 2000-11-16 00:00:00+06
 2000-11-16 00:00:00+06
 2000-11-16 00:00:00+06
 2000-11-17 00:00:00+06
 2000-11-17 00:00:00+06
 2000-11-20 00:00:00+06
 2000-11-20 00:00:00+06
 2000-11-21 00:00:00+06
 2000-11-21 00:00:00+06
 2000-11-22 00:00:00+06
 2000-11-22 00:00:00+06
 2000-11-22 00:00:00+06
 2000-11-23 00:00:00+06
 2000-11-23 00:00:00+06
 2000-11-24 00:00:00+06
 2000-11-24 00:00:00+06
 2000-11-27 00:00:00+06
 2000-11-27 00:00:00+06
 2000-11-27 00:00:00+06
 2000-11-28 00:00:00+06
 2000-11-28 00:00:00+06
 2000-11-28 00:00:00+06
 2000-11-29 00:00:00+06
 2000-11-29 00:00:00+06
 2000-11-30 00:00:00+06
 2000-11-30 00:00:00+06
 2000-11-30 00:00:00+06
 2000-11-30 00:00:00+06
 2000-11-30 00:00:00+06
(203 rows)

billing=> select InDate from FirmICO where date_part('year', indate)=2000 and date_part('quarter', indate)=4 ORDER BY
InDate;
         indate
------------------------
 2000-12-04 00:00:00+06
 2000-12-04 00:00:00+06
 2000-12-04 00:00:00+06
 2000-12-05 00:00:00+06
 2000-12-05 00:00:00+06
 2000-12-06 00:00:00+06
 2000-12-06 00:00:00+06
 2000-12-06 00:00:00+06
 2000-12-06 00:00:00+06
 2000-12-07 00:00:00+06
 2000-12-08 00:00:00+06
 2000-12-08 00:00:00+06
 2000-12-09 00:00:00+06
 2000-12-09 00:00:00+06
 2000-12-13 00:00:00+06
 2000-12-13 00:00:00+06
 2000-12-14 00:00:00+06
 2000-12-14 00:00:00+06
 2000-12-15 00:00:00+06
 2000-12-15 00:00:00+06
 2000-12-18 00:00:00+06
 2000-12-18 00:00:00+06
 2000-12-18 00:00:00+06
 2000-12-19 00:00:00+06
 2000-12-20 00:00:00+06
 2000-12-20 00:00:00+06
 2000-12-20 00:00:00+06
 2000-12-21 00:00:00+06
 2000-12-21 00:00:00+06
 2000-12-22 00:00:00+06
 2000-12-22 00:00:00+06
 2000-12-22 00:00:00+06
 2000-12-22 00:00:00+06
 2000-12-25 00:00:00+06
 2000-12-25 00:00:00+06
 2000-12-25 00:00:00+06
 2000-12-26 00:00:00+06
 2000-12-27 00:00:00+06
 2000-12-27 00:00:00+06
 2000-12-27 00:00:00+06
 2000-12-27 00:00:00+06
 2000-12-27 00:00:00+06
 2000-12-28 00:00:00+06
 2000-12-29 00:00:00+06
 2000-12-29 00:00:00+06
(45 rows)

Denis Osadchy
Russia, Novosibirsk

> I tried to use function date_part('quarter', date) in query and I have
> got wrong results...

Yeesh! Don't know what I was thinking at the time, but it sure is wrong.
If you have PostgreSQL built from source, then you can fix this by
editing src/backend/utils/timestamp.c on or around line 2056. Change a
single line, just under the "case DTK_QUARTER:" from

  result = (tm->tm_mon / 4) + 1;

to

  result = ((tm->tm_mon - 1) / 3) + 1;

and you should start getting the right answer. Will be fixed in the next
release.

                       - Thomas

> I tried to use function date_part('quarter', date) in query and I have
> got wrong results - for first quarter I've got all rows from 01.01 to
> 31.03
> for second - from from 01.04 to 31.07 (!!!)
> for third  - from from 01.08 to 30.11 (!!!)
> for forth  - from from 01.12 to 31.12 (!!!)
> -----------------------
> PostgreSQL 7.0.2 on i386-pc-bsdi4.0.1, compiled by gcc 2.7.2.1
> -----------------------

 until bug will fix, in 7.0.2 you can use:

    ... WHERE to_char(field, 'YYYY Q') = '2000 1'

 (it's - may be - faster than 2x date_part())

            Karel

> I tried to use function date_part('quarter', date) in query and I have
> got wrong results...

Yeesh! Don't know what I was thinking at the time, but it sure is wrong.
If you have PostgreSQL built from source, then you can fix this by
editing src/backend/utils/timestamp.c on or around line 2056. Change a
single line, just under the "case DTK_QUARTER:" from

  result = (tm->tm_mon / 4) + 1;

to

  result = ((tm->tm_mon - 1) / 3) + 1;

and you should start getting the right answer. Will be fixed in the next
release.

                       - Thomas