Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ihH5l-0004AC-FH for pgsql-bugs@arkaria.postgresql.org; Tue, 17 Dec 2019 17:57:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ihH5k-0002TU-4T for pgsql-bugs@arkaria.postgresql.org; Tue, 17 Dec 2019 17:57:04 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ihH5j-0002TN-Oa for pgsql-bugs@lists.postgresql.org; Tue, 17 Dec 2019 17:57:03 +0000 Received: from mail-wr1-x441.google.com ([2a00:1450:4864:20::441]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ihH5h-0007bi-9F for pgsql-bugs@lists.postgresql.org; Tue, 17 Dec 2019 17:57:02 +0000 Received: by mail-wr1-x441.google.com with SMTP id b6so12340178wrq.0 for ; Tue, 17 Dec 2019 09:57:00 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=2ndquadrant-com.20150623.gappssmtp.com; s=20150623; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to; bh=kf9EKaHC2KVH+GNss9EX1N87akSXtRxPxwG7LklWsbI=; b=O2Amk8fZmUU52YYnAGEFK/sMRADk46ms98twd1xV0eLrSJfNnRm8D9BcpakOQwL3Me Nctfv66CoqG7yhaVD8qhnf8l4R6ff9ep0fe8eOhLjUJAp2WLTKnhqTczZD+RbEyPpGmy +PrLKOTyoShTzXFKXmOSx7JaKjHz7E60IZHR2ySXicpmyllYtcuQHsglGWaw5OZTQ6aa EF05m8MOKI0D7bN8MSQJzlFY2f2oMQyaWUq4tZLuTK+pcWvPuPSZr2yyfJFsqHlGcxrl NdHX4W8p1OHImOJF15YrCVvKE9joTQ2A/D3gauH6mj5ml/qOXr/LzgXfrAEh4TS0DnQ8 hZrA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to; bh=kf9EKaHC2KVH+GNss9EX1N87akSXtRxPxwG7LklWsbI=; b=DAYe+uGxyg3r6rR5yiPZBEZuteZs9ZfUBX1ewAqt2Zptjp68uSuhhEYAAqb1at0c7k O3IQOcHWDE/7FE68py4922gl//7NEaRynUoDkxs0C0cE3uuFzTm4Sbcosv94oMJZmbsi sooC/XNJXTgEtohmMp1nQunFUq8hiRWrA2Mt1rhAtQqvoK984LqOhMPjdlQgHTupWdaj Tpq66epdczC+FVFh3axOQIN/ujnEfSL0L0EQwiAO63VQvZHzPfKALhzQGENShGkhIBnm uCWe2Xuo1iprfVB2uwdUbLxsEZQd/gUx3REZndOx07Z9FKdnowiH5DHb8+DXJ2AVynFV EIIg== X-Gm-Message-State: APjAAAWYz5hfbBKPFGFwwe5p2w8KTwOVJXIG0TK6GVQSt1xs5L7mSz+V ba02cmng8h5uuklM/9z90myWmA== X-Google-Smtp-Source: APXvYqwBKIgK+nDQxJqRJEgHMeObITBdKfRRUxpKxDWiJiZ26gok0MjCrB0y6NwIYEKy2TqiG3jJ5g== X-Received: by 2002:adf:fd07:: with SMTP id e7mr37706506wrr.21.1576605419010; Tue, 17 Dec 2019 09:56:59 -0800 (PST) Received: from localhost (static-84-42-175-93.net.upcbroadband.cz. [84.42.175.93]) by smtp.gmail.com with ESMTPSA id z11sm26657302wrt.82.2019.12.17.09.56.58 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Tue, 17 Dec 2019 09:56:58 -0800 (PST) Date: Tue, 17 Dec 2019 18:56:55 +0100 From: Tomas Vondra To: Alvaro Herrera Cc: folarte@peoplecall.com, pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16169: Default time output for 24:00 is 00:00 Message-ID: <20191217175655.z74ftoqx5qhldln2@development> References: <16169-db6039a4786ae5d7@postgresql.org> <20191217172607.GA10657@alvherre.pgsql> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Disposition: inline In-Reply-To: <20191217172607.GA10657@alvherre.pgsql> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Tue, Dec 17, 2019 at 02:26:07PM -0300, Alvaro Herrera wrote: >On 2019-Dec-17, PG Bug reporting form wrote: > >> The output correctly follows what the manual says ( >> https://www.postgresql.org/docs/12/datatype-datetime.html ). Although the >> doc is correct, I have not been able to find any explicit mention of this >> behaviour. An explicit mention may be useful to avoid confusion, as one >> normally expects to be able to reconstruct inserted data from select results >> ( changing behaviour may hurt existing dependencies on current one ), and >> 2400 may be a common value when using time fields for things like "opening >> hours 16:00 - 24:00". > >Do you have a concrete proposal? I think 8.5.1.2 could have a new para >saying "note that 24:00 is displayed as 00:00 because it really is the >start of the next day, and there's no way to distinguish that from 00:00 >of the current day." Or maybe it should just state that using a time >column without an accompanying date column is a lost cause. > Yeah, I was wondering about the same thing. There's nothing like '24:00' for datetime (except when there's an extra second for a given day, perhaps). If someone needs to show opening hours or something, I don't see why to use datetime, we have 'time' data type for that, and that seems to be working fine: test=# select to_char('24:00:00'::time, 'HH24:MI:SS'); to_char ---------- 24:00:00 (1 row) Maybe I'm missing something, of course. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services