Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rfpVW-002Zf1-AT for pgsql-general@arkaria.postgresql.org; Thu, 29 Feb 2024 23:08:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rfpVT-009NKd-W2 for pgsql-general@arkaria.postgresql.org; Thu, 29 Feb 2024 23:08:04 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rfpVT-009NKU-HE for pgsql-general@lists.postgresql.org; Thu, 29 Feb 2024 23:08:04 +0000 Received: from mail-pl1-x632.google.com ([2607:f8b0:4864:20::632]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rfpVM-002EFj-4N for pgsql-general@postgresql.org; Thu, 29 Feb 2024 23:08:02 +0000 Received: by mail-pl1-x632.google.com with SMTP id d9443c01a7336-1dc0d11d1b7so13675315ad.2 for ; Thu, 29 Feb 2024 15:07:56 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1709248076; x=1709852876; darn=postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=N1ICalPhvMohRpOz/ny904xXz3swj7M1xHWAz8/5Rcc=; b=ncUQvx7PQB4+h/5zV8VwzR4kFPIsF8ys5gX/INzzt5NmX0o+q1/dYzH7ar8mujj7b2 noNoZmyLutCTbO1rkvJXVlQJxWQEmEl+86c8c0ykPOXS+OgBjkm/EtTqC/h0mVG0Wxxc WGWqhZGb04MD/tgg0PTUFUPYgOWAoA3XuhBNqYvszPjpYMqkVst/++XdfWOTeSpTMATN DYcTq2Q/kKFxA2px/P0SR2WB7MngfnrNwh2gSkxqR2ukfzYcR2ZpJbYV1B5B9LK7mp/z srbRrOVBfspJ5jStRDbDXhwkp8I/Dbqdq6PqYch2X+l/rBV8+Sa3O9UOS108UKKQPqhX Ld9A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1709248076; x=1709852876; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=N1ICalPhvMohRpOz/ny904xXz3swj7M1xHWAz8/5Rcc=; b=McT23VQVyfcfhIPzsO8Mt266XKG/qn/+89yoa90/w8aTkGb3hFXJti4Q7Ba2RcidLD KA+yDZwDbpqB9X8TnGErfStKom+rE8loWh+1OBTixF1+lOjNTgABmYdCKh7xGaZiiZZN fOJWDvxMmlNm39YK7hziqjWmVDpYMikeCKfXjFXkSINzeqyxjZg39niRVgR8JdOatMw3 zfZhsCy0/081iR+33cN4GbDSg573ZNtFWJ+C42D3QxvaPaVCNSerf01SQFev1r4gwqu0 R0OEdvVjlfYrcseD6EjD1ki7f8oAhSvUVEqE4KjxWQ4FE+cKcrwlQHKBlFTHa9+ixQPC wFgw== X-Forwarded-Encrypted: i=1; AJvYcCXWY2VfLAY1mWsSg+2O0bN8Bep76Y41c6MFYP3LpZlSfBeaKDIHSmRlIiUBpQUCdb380a0kL4p2KjQcS8CvH9u5jBbwZbHbDtSxacKm X-Gm-Message-State: AOJu0YxMjEalA1AnAdze+paYrY23XCaiJ8RVsDS5LIQE46HUsZGGb6JC KjBcZNdBAxfB+70o03FXlROPVuEJCrgPBoHHkzoSfNfeE+zn5a4f X-Google-Smtp-Source: AGHT+IGaRC9/IG1bmEk1JKZ2OEcnY2Xhu4sXFAKHWNw3yV//TCimyP1qST3UWlPkILlxq6mK/byWWg== X-Received: by 2002:a17:903:41ce:b0:1db:c6a0:288b with SMTP id u14-20020a17090341ce00b001dbc6a0288bmr4382024ple.7.1709248075924; Thu, 29 Feb 2024 15:07:55 -0800 (PST) Received: from smtpclient.apple ([2600:8801:8600:631::a]) by smtp.gmail.com with ESMTPSA id l4-20020a170902f68400b001dcbffec642sm2038742plg.133.2024.02.29.15.07.54 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Thu, 29 Feb 2024 15:07:55 -0800 (PST) From: Guyren Howe Message-Id: <201DD701-98E4-4349-9CF3-DBBF80700992@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_5191FFE7-19B6-4A4F-A13D-A102E8279ECA" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.300.61.1.2\)) Subject: Re: Content for talk on Postgres Type System at PostgresConf Date: Thu, 29 Feb 2024 15:07:29 -0800 In-Reply-To: <2758726.1709247071@sss.pgh.pa.us> Cc: "David G. Johnston" , Postgres General , Laurenz Albe To: Tom Lane References: <08e99a8fa744e063ea31030d6516bfc64c26e3f8.camel@cybertec.at> <372fd5d1-8d7c-411e-8a35-41793978e74b@Spark> <2758726.1709247071@sss.pgh.pa.us> X-Mailer: Apple Mail (2.3774.300.61.1.2) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_5191FFE7-19B6-4A4F-A13D-A102E8279ECA Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 On 29 Feb 2024, at 14:51, Tom Lane wrote: >=20 >>> - time with time zone *does* store the time zone, but this isn=E2=80=99= t >>> actually useful and should be avoided (I=E2=80=99m not entirely sure = why and the >>> docs only gesture at the problems without stating them, IIRC) >=20 >> No it doesn't store the time zone. Nor do the docs say they do. And >> clearly point out the issue that evaluating a time zone without both = date >> and time inputs is basically pointless. >=20 > timetz *does* store a time zone, in the sense of storing a numeric > offset from UTC (i.e., "so many minutes east or west of Greenwich"). > The problem is that in most real-world applications your notion of > "time zone" probably includes annual DST changes, which timetz can't > represent. I don't say the type is completely useless, but its > usefulness is a lot less than you might guess. The closest I can come to this in the docs is: "The appropriate time zone offset is recorded in the time with time zone = value and is output as stored; it is not adjusted to the active time = zone.=E2=80=9D I expect to be submitting some documentation updates as part of this = project, fwiw.= --Apple-Mail=_5191FFE7-19B6-4A4F-A13D-A102E8279ECA Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 On 29 Feb = 2024, at 14:51, Tom Lane <tgl@sss.pgh.pa.us> = wrote:

- time with time zone = *does* store the time zone, but this isn=E2=80=99t
actually useful = and should be avoided (I=E2=80=99m not entirely sure why and the
docs = only gesture at the problems without stating them, = IIRC)

No it doesn't store the time zone.  Nor do = the docs say they do.  And
clearly point out the issue that = evaluating a time zone without both date
and time inputs is basically = pointless.

timetz = *does* store a time zone, in the sense of storing a numeric
offset from UTC (i.e., "so many minutes = east or west of Greenwich").
The = problem is that in most real-world applications your notion of
"time zone" probably includes annual DST = changes, which timetz can't
represent.  I don't say the type is completely = useless, but its
usefulness is a lot less than you might guess.

The closest I can come to = this in the docs is:

"The appropriate time zone offset is = recorded in the time with time zone value and is output as stored; it = is not adjusted to the active time zone.=E2=80=9D

I expect to be submitting some = documentation updates as part of this project, = fwiw.
= --Apple-Mail=_5191FFE7-19B6-4A4F-A13D-A102E8279ECA--