On Jul 26, 2011, at 5:56 PM, Christopher Browne wrote:
>> I'm assuming that the issue here is that multiple backends could be
>> connected to the same database, and we don't want all of them to try to
>> actually do the updates, only the first one that discovers the change. If
>> that's the prob
On Tue, Jul 26, 2011 at 6:45 PM, Jim Nasby wrote:
> On Jul 25, 2011, at 6:22 PM, Robert Haas wrote:
>> On Mon, Jul 25, 2011 at 6:26 PM, Jim Nasby wrote:
>>> Hrm, don't we only pull in ZIC info on a reload? Or do we actually refer to
>>> it dynamically all the time? Perhaps we can enforce that we
On Jul 25, 2011, at 6:22 PM, Robert Haas wrote:
> On Mon, Jul 25, 2011 at 6:26 PM, Jim Nasby wrote:
>> Hrm, don't we only pull in ZIC info on a reload? Or do we actually refer to
>> it dynamically all the time? Perhaps we can enforce that we'll only
>> recognize new TZ info as part of a config r
On Mon, Jul 25, 2011 at 6:26 PM, Jim Nasby wrote:
> Hrm, don't we only pull in ZIC info on a reload? Or do we actually refer to
> it dynamically all the time? Perhaps we can enforce that we'll only recognize
> new TZ info as part of a config reload?
Hmm. That might work in theory, but I don't
On Jul 22, 2011, at 10:33 AM, Robert Haas wrote:
> On Thu, Jul 21, 2011 at 7:05 PM, Jim Nasby wrote:
>> On Jul 21, 2011, at 5:30 PM, Christopher Browne wrote:
>>> - I'd commend capturing NOW() in a timestamptz field. That gives you:
>>> 1. What time the DB server thought it was, in terms of UT1
Jim,
> Arguably, we could just create an add-on data type for storing that timezone
> information, but that seems pretty daft to me: you're stuck either storing
> raw text which takes what should be a 12 byte datatype up to a 20-30 byte
> type (8 byte timestamp + varlena + text of timezone name
Excerpts from Robert Haas's message of vie jul 22 11:33:09 -0400 2011:
> This problem reminds me a great deal of the problems associated with
> managing security labels for SE-Linux. There aren't that many
> distinct values, so ideally it would be nice to store an OID -> string
> mapping somewher
On Thu, Jul 21, 2011 at 7:05 PM, Jim Nasby wrote:
> On Jul 21, 2011, at 5:30 PM, Christopher Browne wrote:
>> - I'd commend capturing NOW() in a timestamptz field. That gives you:
>> 1. What time the DB server thought it was, in terms of UT1
>> 2. What timezone it thought was tied to that conne
On Jul 21, 2011, at 5:30 PM, Christopher Browne wrote:
> - I'd commend capturing NOW() in a timestamptz field. That gives you:
> 1. What time the DB server thought it was, in terms of UT1
> 2. What timezone it thought was tied to that connection.
Except that it doesn't, and that's exactly the p
On Thu, Jul 21, 2011 at 5:48 PM, Jim Nasby wrote:
> On Jul 19, 2011, at 4:06 PM, Josh Berkus wrote:
>>> I have my doubts about that, and I hope not. These details haven't been
>>> discussed at all; I only started this thread to get community approval
>>> on cataloguing the TZs.
>>
>> I am strongl
Jim Nasby writes:
> On Jul 19, 2011, at 11:22 AM, Ian Caulfield wrote:
>> There was an earlier point made that if someone puts eg 5pm local time
>> two years in the future into the database, and then the DST boundary
>> gets moved subsequently, some applications would like the value to
>> still sa
On Jul 19, 2011, at 4:06 PM, Josh Berkus wrote:
>> I have my doubts about that, and I hope not. These details haven't been
>> discussed at all; I only started this thread to get community approval
>> on cataloguing the TZs.
>
> I am strongly in favor of having a *timezone* data type and some syst
On Jul 21, 2011, at 2:39 PM, Jim Nasby wrote:
>> CREATE OR REPLACE FUNCTION is_timezone(
>> tz CITEXT
>> ) RETURNS BOOLEAN LANGUAGE plpgsql STABLE AS $$
> ...
>> CREATE DOMAIN timezone AS CITEXT CHECK ( is_timezone( VALUE ) );
>
> Storing giant globs of text with every timestamp field is really
On Jul 19, 2011, at 11:22 AM, Ian Caulfield wrote:
> On 19 July 2011 17:11, Kevin Grittner wrote:
>> Tom Lane wrote:
>>> "Kevin Grittner" writes:
Josh Berkus wrote:
> The timestamp and the timezone in which that timestamp was
> entered are two separate pieces of data and *ought* to
On Jul 19, 2011, at 4:20 PM, David E. Wheeler wrote:
> On Jul 19, 2011, at 2:06 PM, Josh Berkus wrote:
>
>> I am strongly in favor of having a *timezone* data type and some system
>> whereby we can uniquely identify timezones in the Zic database.
>
> CREATE OR REPLACE FUNCTION is_timezone(
>t
On Jul 19, 2011, at 2:06 PM, Josh Berkus wrote:
> I am strongly in favor of having a *timezone* data type and some system
> whereby we can uniquely identify timezones in the Zic database.
CREATE OR REPLACE FUNCTION is_timezone(
tz CITEXT
) RETURNS BOOLEAN LANGUAGE plpgsql STABLE AS $$
BEGIN
Alvaro, Kevin,
>> In a builtin data type, which of those three would you pick? Only the
>> application knows.
>
> I think this whole discussion is built on the assumption that the client
> timezone and the application timezone are one thing and the same; and
> the server timezone is not relevant
On 19 July 2011 17:11, Kevin Grittner wrote:
> Tom Lane wrote:
>> "Kevin Grittner" writes:
>>> Josh Berkus wrote:
The timestamp and the timezone in which that timestamp was
entered are two separate pieces of data and *ought* to be in two
separate fields.
>>
>>> So, if you're grab
Tom Lane wrote:
> "Kevin Grittner" writes:
>> Josh Berkus wrote:
>>> The timestamp and the timezone in which that timestamp was
>>> entered are two separate pieces of data and *ought* to be in two
>>> separate fields.
>
>> So, if you're grabbing a timestamp and the time zone for it, how
>> do
"Kevin Grittner" writes:
> Josh Berkus wrote:
>> The timestamp and the timezone in which that timestamp was entered
>> are two separate pieces of data and *ought* to be in two separate
>> fields.
> So, if you're grabbing a timestamp and the time zone for it, how do
> you ensure you've done that
Josh Berkus wrote:
> The timestamp and the timezone in which that timestamp was entered
> are two separate pieces of data and *ought* to be in two separate
> fields.
So, if you're grabbing a timestamp and the time zone for it, how do
you ensure you've done that atomically if you're at the boun
Excerpts from Josh Berkus's message of lun jul 18 18:37:15 -0400 2011:
> The timestamp and the timezone in which that timestamp was entered are
> two separate pieces of data and *ought* to be in two separate fields.
> For one thing, the question of "what timezone was this entered in" is an
> appli
Jim,
> Right; you need a timestamp and you need to know what timezone that timestamp
> was entered in. That means you can always convert that time to whatever
> timezone you'd like (like timestamptz), but you also know what time was
> originally entered, and what timezone it was entered in. Tec
On Jul 18, 2011, at 12:29 AM, Robert Haas wrote:
> On Fri, Jul 8, 2011 at 11:13 AM, Stuart Bishop
> wrote:
>> On Mon, Jun 6, 2011 at 7:50 AM, Jim Nasby wrote:
>>> On Jun 4, 2011, at 3:56 AM, Greg Stark wrote:
On Thu, Jun 2, 2011 at 8:58 PM, Jim Nasby wrote:
>
> I'm torn between wh
On Fri, Jul 8, 2011 at 11:13 AM, Stuart Bishop wrote:
> On Mon, Jun 6, 2011 at 7:50 AM, Jim Nasby wrote:
>> On Jun 4, 2011, at 3:56 AM, Greg Stark wrote:
>>> On Thu, Jun 2, 2011 at 8:58 PM, Jim Nasby wrote:
I'm torn between whether the type should store the original time or the
o
On Jul 6, 2011, at 9:24 AM, Alexey Klyukin wrote:
> So, I'd think there are 2 reasonable approaches to storing the
> timezone part:
>
> 1. Store the timezone abbreviation (i.e. 'EST' along w/ the timestamp
> data).
> 2. Assign OID to each of the timezones and store it w/ the timestamp.
>
> The f
On Mon, Jun 6, 2011 at 7:50 AM, Jim Nasby wrote:
> On Jun 4, 2011, at 3:56 AM, Greg Stark wrote:
>> On Thu, Jun 2, 2011 at 8:58 PM, Jim Nasby wrote:
>>>
>>> I'm torn between whether the type should store the original time or the
>>> original time converted to GMT.
>>
>> This is the wrong way to
Hi,
On May 27, 2011, at 11:43 PM, Alvaro Herrera wrote:
>
> One of our customers is interested in being able to store original
> timezone along with a certain timestamp.
>
> It is currently possible to store a TZ in a separate column, but this is
> a bit wasteful and not very convenient anyway.
On Jun 4, 2011, at 3:56 AM, Greg Stark wrote:
> On Thu, Jun 2, 2011 at 8:58 PM, Jim Nasby wrote:
>>
>> I'm torn between whether the type should store the original time or the
>> original time converted to GMT.
>
> This is the wrong way to think about it. We *never* store time
> "converted to GM
On Thu, Jun 2, 2011 at 8:58 PM, Jim Nasby wrote:
>
> I'm torn between whether the type should store the original time or the
> original time converted to GMT.
This is the wrong way to think about it. We *never* store time
"converted to GMT". When we want to represent a point in time we
represen
On tor, 2011-06-02 at 22:58 -0500, Jim Nasby wrote:
> I'm torn between whether the type should store the original time or
> the original time converted to GMT. I believe you would have the most
> accuracy if you stored the original time... but then indexing becomes
> problematic. I don't know if th
On Jun 2, 2011, at 7:48 PM, Jeff Davis wrote:
> On Thu, 2011-06-02 at 20:28 -0400, Robert Haas wrote:
>> But that doesn't seem like enough, because if someone adds '1 day',
>> knowing the offset isn't sufficient to figure out the answer. You
>> have to know where the DST boundary is.
>
> Good poi
On Thu, 2011-06-02 at 20:28 -0400, Robert Haas wrote:
> But that doesn't seem like enough, because if someone adds '1 day',
> knowing the offset isn't sufficient to figure out the answer. You
> have to know where the DST boundary is.
Good point, I guess the timezone itself needs to be stored. Tha
On Thu, Jun 2, 2011 at 3:02 PM, Jeff Davis wrote:
> On Thu, 2011-06-02 at 18:46 +, Christopher Browne wrote:
>> > 1. How would the time-zone be defined in this composite? Offset from GMT?
>> > Timezone (well, link thereto) with all DST rules intact? Would "extract"
>> > need to be modified to
On Thu, Jun 2, 2011 at 12:55 PM, Alvaro Herrera
wrote:
>> >> > One of our customers is interested in being able to store original
>> >> > timezone along with a certain timestamp.
>> >>
>> >> I assume that you're talking about a new data type, not augmenting the
>> >> current types, correct?
>> >
>
On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote:
> One of our customers is interested in being able to store original
> timezone along with a certain timestamp.
Another thing to consider is that this will eliminate any useful total
order.
You could define an arbitrary total order, of cour
On Thu, Jun 2, 2011 at 6:06 PM, Steve Crawford
wrote:
> On 06/01/2011 05:18 PM, Alvaro Herrera wrote:
>>
>> Excerpts from Jeff Davis's message of mié jun 01 19:57:40 -0400 2011:
>>>
>>> On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote:
Hi,
One of our customers is interes
On Thu, 2011-06-02 at 18:46 +, Christopher Browne wrote:
> > 1. How would the time-zone be defined in this composite? Offset from GMT?
> > Timezone (well, link thereto) with all DST rules intact? Would "extract"
> > need to be modified to include the ability to grab the timezone?
>
> That does
Excerpts from Merlin Moncure's message of mié jun 01 21:36:32 -0400 2011:
> On Wed, Jun 1, 2011 at 8:18 PM, Alvaro Herrera
> wrote:
> > Excerpts from Jeff Davis's message of mié jun 01 19:57:40 -0400 2011:
> >> On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote:
> >> > Hi,
> >> >
> >> > One o
On 06/01/2011 05:18 PM, Alvaro Herrera wrote:
Excerpts from Jeff Davis's message of mié jun 01 19:57:40 -0400 2011:
On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote:
Hi,
One of our customers is interested in being able to store original
timezone along with a certain timestamp.
I assume
On Wed, Jun 1, 2011 at 8:18 PM, Alvaro Herrera
wrote:
> Excerpts from Jeff Davis's message of mié jun 01 19:57:40 -0400 2011:
>> On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote:
>> > Hi,
>> >
>> > One of our customers is interested in being able to store original
>> > timezone along with a
On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote:
> Hi,
>
> One of our customers is interested in being able to store original
> timezone along with a certain timestamp.
I assume that you're talking about a new data type, not augmenting the
current types, correct?
Regards,
Jeff Da
Excerpts from Jeff Davis's message of mié jun 01 19:57:40 -0400 2011:
> On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote:
> > Hi,
> >
> > One of our customers is interested in being able to store original
> > timezone along with a certain timestamp.
>
> I assume that you're talking about a
On 05/28/2011 02:58 PM, Peter Eisentraut wrote:
On fre, 2011-05-27 at 16:57 -0700, Steve Crawford wrote:
And the second case is already well handled. In fact calendaring is a
great example. I enter the time for the teleconference and PG nicely
uses my default timezone to store the point-in-time.
On fre, 2011-05-27 at 16:57 -0700, Steve Crawford wrote:
> And the second case is already well handled. In fact calendaring is a
> great example. I enter the time for the teleconference and PG nicely
> uses my default timezone to store the point-in-time. When you
> retrieve
> it, it is shown in
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Fri, May 27, 2011 at 04:43:28PM -0400, Alvaro Herrera wrote:
> Hi,
>
> One of our customers is interested in being able to store original
> timezone along with a certain timestamp.
I've felt that pain here and there too...
> So the first thing is
On May 27, 2011, at 6:29 PM, Greg Stark wrote:
> Both of these two cases can be handled differently. The former by
> storing the raw text inputs and then storing the interpreted value as
> a derived column separetly, and the latter by storing the local time
> zone to use for display as an additiona
On 05/27/2011 04:29 PM, Greg Stark wrote:
On Fri, May 27, 2011 at 4:13 PM, Steve Crawford
wrote:
I am very interested in the use-case for this (in part as I'm working on a
PG related time talk). My experience thus far is that people who want this
do not fully understand the nature of date-time
On Fri, May 27, 2011 at 4:13 PM, Steve Crawford
wrote:
> I am very interested in the use-case for this (in part as I'm working on a
> PG related time talk). My experience thus far is that people who want this
> do not fully understand the nature of date-time calculations and variables
> in PG.
Th
On 05/27/2011 01:43 PM, Alvaro Herrera wrote:
Hi,
One of our customers is interested in being able to store original
timezone along with a certain timestamp.
I am very interested in the use-case for this (in part as I'm working on
a PG related time talk). My experience thus far is that people w
On Fri, May 27, 2011 at 2:32 PM, Kevin Grittner
wrote:
> I think the key thing is that the timestamp portion of it would be
> identical to our current TIMESTAMP WITH TIME ZONE -- always store it
> in the value UTC zone.
Fwiw our timestamp with time zone stores seconds since the epoch. This
is a q
On May 27, 2011, at 2:35 PM, Tom Lane wrote:
> "David E. Wheeler" writes:
>> I like it, but what do you do when a TZ has been renamed or has ceased
>> to exist.
>
> As far as that goes, I think "nothing" is a sufficient answer. There's
> no requirement that an OID in the mapping table correspon
"David E. Wheeler" writes:
> I like it, but what do you do when a TZ has been renamed or has ceased
> to exist.
As far as that goes, I think "nothing" is a sufficient answer. There's
no requirement that an OID in the mapping table correspond to a live TZ.
It's just a more compact way of storing
"David E. Wheeler" wrote:
> I like it, but what do you do when a TZ has been renamed or has
> ceased to exist. Or, worse, existed last week, so last week's
> dates might still use it, but next week's must not?
I think the key thing is that the timestamp portion of it would be
identical to our
On May 27, 2011, at 1:43 PM, Alvaro Herrera wrote:
> Right now we rely on the tzdata files on disk for things like
> pg_timezone_names and other accesses of TZ data; so the files are the
> authoritative source of TZ info. So we need to ensure that whenever the
> files are updated, the catalogs ar
Alvaro Herrera writes:
> Right now we rely on the tzdata files on disk for things like
> pg_timezone_names and other accesses of TZ data; so the files are the
> authoritative source of TZ info. So we need to ensure that whenever the
> files are updated, the catalogs are updated as well.
> I thi
Hi,
One of our customers is interested in being able to store original
timezone along with a certain timestamp.
It is currently possible to store a TZ in a separate column, but this is
a bit wasteful and not very convenient anyway.
There are all sorts of UI issues that need to be resolved in ord
57 matches
Mail list logo
