{"id":6519,"date":"2026-08-29T13:53:35","date_gmt":"2026-08-29T04:53:35","guid":{"rendered":"https:\/\/route-zero.com\/recruit\/?post_type=route&p=6519"},"modified":"2026-10-29T13:53:45","modified_gmt":"2026-10-29T04:53:45","slug":"postgresql-date-trunc-monthly-group-by","status":"publish","type":"route","link":"https:\/\/route-zero.com\/recruit\/route\/6519\/","title":{"rendered":"PostgreSQL\u306edate_trunc\u3067\u6708\u5225\u96c6\u8a08\uff5c\u6ce8\u6587\u30c7\u30fc\u30bf\u3092\u6570\u3048\u308bSQL\u4f8b"},"content":{"rendered":"

\u300c\u6ce8\u6587\u65e5\u6642\u306f\u65e5\u4ed8\u3068\u6642\u523b\u307e\u3067\u4fdd\u5b58\u3055\u308c\u3066\u3044\u308b\u306e\u306b\u3001\u6708\u3054\u3068\u306e\u4ef6\u6570\u3092\u6570\u3048\u305f\u3044\u300d\u3002PostgreSQL\u3067\u306f`date_trunc('month', \u65e5\u6642)`\u3067\u65e5\u6642\u3092\u6708\u521d\u3078\u305d\u308d\u3048\u3001`GROUP BY`\u3067\u540c\u3058\u6708\u306e\u884c\u3092\u307e\u3068\u3081\u3089\u308c\u307e\u3059\u3002\u3053\u306e\u8a18\u4e8b\u3067\u306f\u3001\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u3089\u305a\u8a66\u305b\u308b\u30b5\u30f3\u30d7\u30ebSQL\u3068\u3001\u305d\u306e\u7d50\u679c\u306e\u8aad\u307f\u65b9\u3092\u8aac\u660e\u3057\u307e\u3059\u3002<\/p>\n

\u7d50\u8ad6\uff1adate_trunc\u3067\u6708\u521d\u306b\u305d\u308d\u3048\u3066\u304b\u3089GROUP BY\u3059\u308b<\/h2>\n

`date_trunc`\u306f\u65e5\u4ed8\u6642\u523b\u3092\u6307\u5b9a\u3057\u305f\u5358\u4f4d\u306b\u5207\u308a\u6368\u3066\u308bPostgreSQL\u306e\u95a2\u6570\u3067\u3059\u3002`month`\u3092\u6307\u5b9a\u3059\u308b\u3068\u3001\u540c\u3058\u5e74\u30fb\u540c\u3058\u6708\u306e\u65e5\u6642\u306f\u305d\u306e\u6708\u306e1\u65e50\u66420\u5206\u306b\u305d\u308d\u3044\u307e\u3059\u3002<\/p>\n

SELECT date_trunc('month', TIMESTAMP '2026-02-23 14:35:20');\r\n-- \u60f3\u5b9a\u7d50\u679c: 2026-02-01 00:00:00<\/code><\/pre>\n
\u6708\u5225\u96c6\u8a08\u3067\u306f\u3001\u3053\u306e\u305d\u308d\u3048\u305f\u6708\u521d\u5024<\/strong>\u3092`GROUP BY`\u306e\u30ad\u30fc\u306b\u3057\u307e\u3059\u3002`EXTRACT(MONTH FROM …)`\u3067\u6708\u306e\u6570\u5b57\u3060\u3051\u3092\u53d6\u308a\u51fa\u3059\u5834\u5408\u3068\u9055\u3044\u3001`date_trunc`\u306a\u30892025\u5e741\u6708\u30682026\u5e741\u6708\u3092\u8aa4\u3063\u3066\u540c\u3058\u30b0\u30eb\u30fc\u30d7\u306b\u3059\u308b\u306e\u3092\u907f\u3051\u3084\u3059\u304f\u306a\u308a\u307e\u3059\u3002<\/p>\n
\n
\u7528\u8a9e\u89e3\u8aac\uff1aGROUP BY<\/strong>
\u540c\u3058\u5024\u3092\u6301\u3064\u884c\u3092\u307e\u3068\u3081\u3001COUNT\u3084SUM\u306a\u3069\u306e\u96c6\u8a08\u95a2\u6570\u3067\u4ef6\u6570\u30fb\u5408\u8a08\u3092\u8a08\u7b97\u3059\u308bSQL\u306e\u6307\u5b9a\u3067\u3059\u3002<\/p>\n<\/blockquote>\n
\n
\u6e96\u5099\u306a\u3057\u3067\u52d5\u304b\u305b\u308b\u6708\u5225\u96c6\u8a08SQL<\/h2>\n
\u4ee5\u4e0b\u306fPostgreSQL\u306eSQL\u5165\u529b\u753b\u9762\uff08`psql`\u3084SQL\u30a8\u30c7\u30a3\u30bf\u30fc\uff09\u3067\u5b9f\u884c\u3067\u304d\u307e\u3059\u3002`WITH orders AS (…)`\u306f\u3053\u306e\u30af\u30a8\u30ea\u3060\u3051\u3067\u4f7f\u3048\u308b\u4eee\u306e\u30c7\u30fc\u30bf\u3092\u7528\u610f\u3059\u308b\u69cb\u6587\u306a\u306e\u3067\u3001\u5b9f\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3057\u305f\u308a\u66f4\u65b0\u3057\u305f\u308a\u3057\u307e\u305b\u3093\u3002<\/p>\n
WITH orders (ordered_at, amount) AS (\r\n  VALUES\r\n    (TIMESTAMP '2026-01-12 10:00:00', 1200),\r\n    (TIMESTAMP '2026-01-28 18:30:00',  800),\r\n    (TIMESTAMP '2026-02-02 09:15:00', 1500)\r\n)\r\nSELECT\r\n  date_trunc('month', ordered_at)::date AS month_start,\r\n  COUNT(*) AS order_count,\r\n  SUM(amount) AS total_amount\r\nFROM orders\r\nGROUP BY date_trunc('month', ordered_at)::date\r\nORDER BY month_start;<\/code><\/pre>\n
\u60f3\u5b9a\u7d50\u679c<\/strong>\u306f1\u6708\u304c2\u4ef6\u30fb\u5408\u8a082000\u30012\u6708\u304c1\u4ef6\u30fb\u5408\u8a081500\u3067\u3059\u3002SQL\u3092\u5b9f\u884c\u3067\u304d\u308b\u74b0\u5883\u304c\u3042\u308c\u3070\u3001\u8fd4\u3063\u305f\u7d50\u679c\u3068\u7167\u5408\u3057\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n\n\n\n\n\n\n
month_start<\/th>\norder_count<\/th>\ntotal_amount<\/th>\n<\/tr>\n<\/thead>\n
2026-01-01<\/td>\n2<\/td>\n2000<\/td>\n<\/tr>\n
2026-02-01<\/td>\n1<\/td>\n1500<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
`::date`\u306fPostgreSQL\u306e\u578b\u5909\u63db\u306e\u66f8\u304d\u65b9\u3067\u3059\u3002\u4eca\u56de\u306f\u6708\u521d\u306e\u65e5\u6642\u306e\u3046\u3061\u65e5\u4ed8\u3060\u3051\u3092\u898b\u3084\u3059\u304f\u8868\u793a\u3057\u3066\u3044\u307e\u3059\u3002\u96c6\u8a08\u30ad\u30fc\u306b\u3082\u540c\u3058\u5909\u63db\u5f0f\u3092\u6307\u5b9a\u3057\u3001\u4f55\u3067\u30b0\u30eb\u30fc\u30d7\u5316\u3057\u305f\u304b\u3092\u660e\u78ba\u306b\u3057\u307e\u3057\u305f\u3002<\/p>\n
\n
\u30af\u30a8\u30ea\u30921\u884c\u305a\u3064\u8aad\u3080\uff1a\u3069\u3053\u3067\u6708\u3092\u307e\u3068\u3081\u3066\u3044\u308b\uff1f<\/h2>\n
    \n
  1. `VALUES`\u3067\u6ce8\u6587\u65e5\u6642\u3068\u91d1\u984d\u306e3\u884c\u3092\u7528\u610f\u3059\u308b\u3002<\/li>\n
  2. `date_trunc`\u3067\u5404\u65e5\u6642\u30922026-01-01\u307e\u305f\u306f2026-02-01\u306b\u305d\u308d\u3048\u308b\u3002<\/li>\n
  3. `GROUP BY`\u3067\u540c\u3058\u6708\u521d\u65e5\u306e\u884c\u3092\u307e\u3068\u3081\u308b\u3002<\/li>\n
  4. `COUNT(*)`\u3067\u884c\u6570\u3092\u3001`SUM(amount)`\u3067\u91d1\u984d\u5408\u8a08\u3092\u8a08\u7b97\u3059\u308b\u3002<\/li>\n
  5. `ORDER BY`\u3067\u53e4\u3044\u6708\u304b\u3089\u8868\u793a\u3059\u308b\u3002<\/li>\n<\/ol>\n
    \u307e\u305a\u306f\u5408\u8a08\u3092\u5916\u3057\u3066`COUNT(*)`\u3060\u3051\u306b\u3057\u3066\u307f\u308b\u3068\u3001\u3069\u306e\u884c\u304c\u540c\u3058\u30b0\u30eb\u30fc\u30d7\u306b\u306a\u308b\u304b\u7406\u89e3\u3057\u3084\u3059\u304f\u306a\u308a\u307e\u3059\u3002\u6b21\u306b\u30b5\u30f3\u30d7\u30eb\u306e1\u4ef6\u30923\u6708\u3078\u5909\u3048\u30013\u304b\u6708\u5206\u306e\u7d50\u679c\u304c\u8fd4\u308b\u304b\u78ba\u8a8d\u3057\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n
    \uff08GROUP BY\u305d\u306e\u3082\u306e\u306b\u6163\u308c\u3066\u3044\u306a\u3044\u5834\u5408\u306b\u3064\u3044\u3066\u306f\u300eGROUP BY\u4f7f\u3044\u65b9\u5b8c\u5168\u89e3\u8aac\uff5cSQL\u30a8\u30e9\u30fc\u30fbWHERE HAVING\u306e\u9055\u3044\u306f\uff1f<\/a>\u300f\u3092\u3054\u53c2\u7167\u304f\u3060\u3055\u3044\uff09<\/p>\n
    \n
    \u6708\u5225\u96c6\u8a08\u304c\u305a\u308c\u308b\u3068\u304d\u306e\u78ba\u8a8d\u70b9<\/h2>\n
    \u6700\u521d\u306b\u3001\u65e5\u6642\u5217\u306e\u578b\u304c`timestamp without time zone`\u304b`timestamp with time zone`\uff08`timestamptz`\uff09\u304b\u3092\u78ba\u304b\u3081\u307e\u3057\u3087\u3046\u3002\u524d\u8005\u306f\u5165\u529b\u3057\u305f\u65e5\u6642\u306e\u6570\u5b57\u3092\u305d\u306e\u307e\u307e\u6271\u3044\u307e\u3059\u3002\u5f8c\u8005\u306e\u6708\u5883\u754c\u306f\u3001\u30bb\u30c3\u30b7\u30e7\u30f3\u306e\u30bf\u30a4\u30e0\u30be\u30fc\u30f3\u8a2d\u5b9a\u306b\u5f71\u97ff\u3055\u308c\u307e\u3059\u3002<\/p>\n
    \u4f8b\u3048\u3070UTC\u3067\u306f1\u670831\u65e523\u664230\u5206\u3067\u3082\u3001\u65e5\u672c\u6642\u9593\u3067\u306f2\u67081\u65e58\u664230\u5206\u3067\u3059\u3002\u65e5\u672c\u6642\u9593\u306e\u58f2\u4e0a\u96c6\u8a08\u3092\u3057\u305f\u3044\u306a\u3089\u3001\u3069\u306e\u30bf\u30a4\u30e0\u30be\u30fc\u30f3\u306e\u6708\u3092\u6570\u3048\u308b\u304b<\/strong>\u3092\u6c7a\u3081\u308b\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059\u3002<\/p>\n
    -- paid_at \u304c timestamptz \u578b\u306e\u5834\u5408\u306e\u8003\u3048\u65b9\r\nSELECT\r\n  date_trunc(\r\n    'month',\r\n    TIMESTAMPTZ '2026-01-31 23:30:00+00'\r\n      AT TIME ZONE 'Asia\/Tokyo'\r\n  )::date AS month_in_japan;\r\n-- \u60f3\u5b9a\u7d50\u679c: 2026-02-01<\/code><\/pre>\n
    \u4e0a\u306e\u4f8b\u3067\u306f`AT TIME ZONE`\u3067\u65e5\u672c\u6642\u9593\u306e\u6642\u523b\u8868\u73fe\u3078\u5909\u3048\u3066\u304b\u3089\u6708\u521d\u306b\u305d\u308d\u3048\u3066\u3044\u307e\u3059\u3002\u5b9f\u30c6\u30fc\u30d6\u30eb\u306e\u5217\u578b\u30fb\u4fdd\u5b58\u65b9\u5f0f\u306b\u5fdc\u3058\u305f\u5909\u63db\u304c\u5fc5\u8981\u3067\u3001\u6642\u523b\u304c\u6700\u521d\u304b\u3089\u65e5\u672c\u6642\u9593\u306e`timestamp`\u5217\u306a\u3089\u4e8c\u91cd\u5909\u63db\u3092\u907f\u3051\u307e\u3059\u3002<\/p>\n