What is the behavior if a column data type is timestamptz but there is
only the date portion available? There must be a default time; can that be
defined?

Rich

Search Discussions

  • Adrian Klaver at Jan 21, 2013 at 3:52 pm

    On 01/21/2013 07:26 AM, Rich Shepard wrote:
    What is the behavior if a column data type is timestamptz but there is
    only the date portion available? There must be a default time; can that be
    defined?
    Easy enough to test:

    test=# create table ts_test(ts_fld timestamp with time zone);
    CREATE TABLE

    test=# insert into ts_test VALUES ('2013-01-21');
    INSERT 0 1

    test=# SELECT * from ts_test ;
               ts_fld
    ------------------------
       2013-01-21 00:00:00-08

    Not sure you can change the default supplied by Postgres, but you can on
       your end:

    test=# insert into ts_test VALUES ('2013-01-21'::date + interval '6' hour);
    INSERT 0 1

    test=# SELECT * from ts_test ;
               ts_fld
    ------------------------
       2013-01-21 00:00:00-08
       2013-01-21 06:00:00-08
    (2 rows)
    Rich


    --
    Adrian Klaver
    adrian.klaver@gmail.com
  • Rich Shepard at Jan 21, 2013 at 3:59 pm

    On Mon, 21 Jan 2013, Adrian Klaver wrote:

    Easy enough to test:
        Thanks again, Adrian.

    Rich
  • Tom Lane at Jan 21, 2013 at 7:27 pm

    Adrian Klaver writes:
    On 01/21/2013 07:26 AM, Rich Shepard wrote:
    What is the behavior if a column data type is timestamptz but there is
    only the date portion available? There must be a default time; can that be
    defined?
    Easy enough to test:
    test=# create table ts_test(ts_fld timestamp with time zone);
    CREATE TABLE
    test=# insert into ts_test VALUES ('2013-01-21');
    INSERT 0 1
    test=# SELECT * from ts_test ;
    ts_fld
    ------------------------
    2013-01-21 00:00:00-08
    Note that that default is local midnight according to your current
    timezone setting (from which we may guess that Adrian lives on the US
    west coast, or somewhere in that general longitude).
    Not sure you can change the default supplied by Postgres,
    "SET timezone" ought to do it ...

        regards, tom lane
  • Rich Shepard at Jan 21, 2013 at 7:37 pm

    On Mon, 21 Jan 2013, Tom Lane wrote:

    Note that that default is local midnight according to your current
    timezone setting (from which we may guess that Adrian lives on the US west
    coast, or somewhere in that general longitude).
        Yep. About 3 hours north of me.
    Not sure you can change the default supplied by Postgres,
    "SET timezone" ought to do it ...
        Thanks, Tom.

    Rich
  • Adrian Klaver at Jan 21, 2013 at 7:53 pm

    On 01/21/2013 11:27 AM, Tom Lane wrote:
    Adrian Klaver <adrian.klaver@gmail.com> writes:
    On 01/21/2013 07:26 AM, Rich Shepard wrote:
    What is the behavior if a column data type is timestamptz but there is
    only the date portion available? There must be a default time; can that be
    defined?
    Easy enough to test:
    test=# create table ts_test(ts_fld timestamp with time zone);
    CREATE TABLE
    test=# insert into ts_test VALUES ('2013-01-21');
    INSERT 0 1
    test=# SELECT * from ts_test ;
    ts_fld
    ------------------------
    2013-01-21 00:00:00-08
    Note that that default is local midnight according to your current
    timezone setting (from which we may guess that Adrian lives on the US
    west coast, or somewhere in that general longitude).
    Not sure you can change the default supplied by Postgres,
    "SET timezone" ought to do it ...
    I took Richs question to mean can you change the time portion supplied
    by Postgres, so:

    Instead of '2013-01-21' having the time portion set to local midnight it
    could be set to a user supplied value say, 08:00:00. That is not
    possible, correct. In the absence of a time portion a date string
    supplied to timestamp will always get local midnight?
    regards, tom lane

    --
    Adrian Klaver
    adrian.klaver@gmail.com
  • Gavan Schneider at Jan 21, 2013 at 10:48 pm
    On Monday, January 21, 2013 at 06:53, Adrian Klaver wrote:
    ....
    On 01/21/2013 11:27 AM, Tom Lane wrote:
    Note that that default is local midnight according to your current
    timezone setting (from which we may guess that Adrian lives on the US
    west coast, or somewhere in that general longitude).
    Not sure you can change the default supplied by Postgres,
    "SET timezone" ought to do it ...
    I took Richs question to mean can you change the time portion supplied by Postgres, so:

    Instead of '2013-01-21' having the time portion set to local midnight
    it could be set to a user supplied value say, 08:00:00. That is not
    possible, correct. In the absence of a time portion a date string
    supplied to timestamp will always get local midnight?
    Thanks to all for the discussion of timestamps with/without
    timezones I have been learning a lot from the side.

    Taking another tangent I would much prefer the default time to
    be 12:00:00 for the conversion of a date to timestamp(+/-timezone).

          Propose: '2013-12-25'::timestamp ==> 2013-12-25 12:00:00

    The benefit of the midday point is that the actual date will not
    change when going through the timezone conversion. This has
    implications for time-of-day insensitive data such as birthdays
    and other calendar values. I am still resolving "off by one day"
    errors that crept into many entries in my calendar and contacts
    from several years ago when data was added while travelling
    across multiple time zones (and I did report it as a bug back
    then). With this lesson learnt the workaround for me in my own
    applications since has been to store such dates as point-in-time
    for midday while keeping track of the input/output so it only
    gets used as a date... sometimes tedious, and a last resort.
    Mostly I have been actively avoiding anything with the taint of
    timezone due to this bad experience. It's time to reconsider, I
    guess, since this can cause other forms of silly behaviour.

    Aesthetically (and/or mathematically) the midday point is more
    accurate. It is the middle of the relevant interval (i.e., 24
    hours) implied by a date. Midnight is the extreme edge of any
    date (i.e., not what you would consider as mid-target).
    "Midnight" also has confusing English semantics since it can
    belong to either of its adjacent days.

    I don't know if the current behaviour will be deemed to be too
    rusted in place for change, or if this proposal has too many
    adverse consequences, but hope springs eternal. :)

    Regards
    Gavan Schneider
  • Steve Crawford at Jan 21, 2013 at 11:53 pm

    On 01/21/2013 02:48 PM, Gavan Schneider wrote:
    On Monday, January 21, 2013 at 06:53, Adrian Klaver wrote:
    ....
    On 01/21/2013 11:27 AM, Tom Lane wrote:
    Note that that default is local midnight according to your current
    timezone setting (from which we may guess that Adrian lives on the US
    west coast, or somewhere in that general longitude).
    Not sure you can change the default supplied by Postgres,
    "SET timezone" ought to do it ...
    I took Richs question to mean can you change the time portion
    supplied by Postgres, so:

    Instead of '2013-01-21' having the time portion set to local midnight
    it could be set to a user supplied value say, 08:00:00. That is not
    possible, correct. In the absence of a time portion a date string
    supplied to timestamp will always get local midnight?
    Thanks to all for the discussion of timestamps with/without timezones
    I have been learning a lot from the side.

    Taking another tangent I would much prefer the default time to be
    12:00:00 for the conversion of a date to timestamp(+/-timezone).

    Propose: '2013-12-25'::timestamp ==> 2013-12-25 12:00:00

    The benefit of the midday point is that the actual date will not
    change when going through the timezone conversion.
    Just like it doesn't change now? (I just checked against all of the more
    than 1,100 zones in PG without seeing a problem.)
    This has implications for time-of-day insensitive data such as
    birthdays and other calendar values. I am still resolving "off by one
    day" errors that crept into many entries in my calendar and contacts
    from several years ago when data was added while travelling across
    multiple time zones (and I did report it as a bug back then). With
    this lesson learnt the workaround for me in my own applications since
    has been to store such dates as point-in-time for midday while keeping
    track of the input/output so it only gets used as a date... sometimes
    tedious, and a last resort. Mostly I have been actively avoiding
    anything with the taint of timezone due to this bad experience. It's
    time to reconsider, I guess, since this can cause other forms of silly
    behaviour.
    Date/time is not trivial. The portions of the PostgreSQL manual dealing
    with those data types bear careful and thoughtful reading and rereading
    while you experiment at the same time in a psql terminal till it
    "clicks." And while some time issues are universal, treatment varies
    from program to program - especially regarding assumptions when the
    input is ambiguous. I'm in the US Pacific time zone so without further
    qualification, "2012-11-04 0130" could be 0130 PST or 0130 PDT.

    The "date" program on my Linux desktop assumes daylight time:
    date -d '2012-11-04 0130'
    Sun Nov 4 01:30:00 PDT 2012

    PostgreSQL assumes standard time:
    select '2012-11-04 0130'::timestamptz;
            timestamptz
    ------------------------
       2012-11-04 01:30:00-08

    Naturally this can lead to all sorts of "fun" when multiple technologies
    are involved.

    Meanwhile if I'm up at that hour and try to schedule a job for immediate
    execution via "at now", the "at" program tells me it is "Cowardly
    refusing to schedule a job in the past." So much for even internal
    consistency.


    Aesthetically (and/or mathematically) the midday point is more
    accurate. It is the middle of the relevant interval (i.e., 24 hours)
    implied by a date. Midnight is the extreme edge of any date (i.e., not
    what you would consider as mid-target). "Midnight" also has confusing
    English semantics since it can belong to either of its adjacent days.
    Except for days that are 23-hours long, or 25, or other (it's a big
    world with all sorts of timezone rules). It's also very useful for
    common queries (select ... from somelog where logtime > current_date)
    and provides a known starting-point from which you can easily calculate
    the offsets you desire.

    BTW It's not at all "more accurate" - it is simply different definition.
    I don't know if the current behaviour will be deemed to be too rusted
    in place for change, or if this proposal has too many adverse
    consequences, but hope springs eternal. :)
    It would sure break a lot of my queries. And for the many people who
    want/expect the date to cast to date at 00:00:00 local time it would
    lead to a load of pitfalls such as naively subtracting 12-hours or
    requiring the programmer to add complexity to determine how many hours
    to subtract based on local time zone and current date.

    But you are, of course, free to use the capability that PostgreSQL gives
    you to define pretty much any data-type you want along with your desired
    casting rules if you so desire. Just don't expect the built-in
    definitions to change.

    Cheers,
    Steve
  • Adrian Klaver at Jan 22, 2013 at 12:38 am

    On 01/21/2013 03:53 PM, Steve Crawford wrote:
    On 01/21/2013 02:48 PM, Gavan Schneider wrote:
    On Monday, January 21, 2013 at 06:53, Adrian Klaver wrote:
    ....
    On 01/21/2013 11:27 AM, Tom Lane wrote:
    Note that that default is local midnight according to your current
    timezone setting (from which we may guess that Adrian lives on the US
    west coast, or somewhere in that general longitude).
    Not sure you can change the default supplied by Postgres,
    "SET timezone" ought to do it ...
    I took Richs question to mean can you change the time portion
    supplied by Postgres, so:

    Instead of '2013-01-21' having the time portion set to local midnight
    it could be set to a user supplied value say, 08:00:00. That is not
    possible, correct. In the absence of a time portion a date string
    supplied to timestamp will always get local midnight?
    Thanks to all for the discussion of timestamps with/without timezones
    I have been learning a lot from the side.

    Taking another tangent I would much prefer the default time to be
    12:00:00 for the conversion of a date to timestamp(+/-timezone).

    Propose: '2013-12-25'::timestamp ==> 2013-12-25 12:00:00

    The benefit of the midday point is that the actual date will not
    change when going through the timezone conversion.
    Just like it doesn't change now? (I just checked against all of the more
    than 1,100 zones in PG without seeing a problem.)
    I must be missing something. I to am in PST:

    test=# \d ts_test
                  Table "utility.ts_test"
       Column | Type | Modifiers
    --------+--------------------------+-----------
       ts_fld | timestamp with time zone |


    test=# INSERT INTO ts_test VALUES('2012-01-21');

    test=# SELECT * from ts_test ;
               ts_fld
    ------------------------
       2012-01-21 00:00:00-08

    test=# set timezone ='AKST9AKDT';

    test=# SELECT ts_fld from ts_test;
               ts_fld
    ------------------------
       2012-01-20 23:00:00-09

    Cheers,
    Steve


    --
    Adrian Klaver
    adrian.klaver@gmail.com
  • Adrian Klaver at Jan 22, 2013 at 12:50 am

    On 01/21/2013 03:53 PM, Steve Crawford wrote:
    On 01/21/2013 02:48 PM, Gavan Schneider wrote:
    On Monday, January 21, 2013 at 06:53, Adrian Klaver wrote:
    ....
    On 01/21/2013 11:27 AM, Tom Lane wrote:
    Note that that default is local midnight according to your current
    timezone setting (from which we may guess that Adrian lives on the US
    west coast, or somewhere in that general longitude).
    Not sure you can change the default supplied by Postgres,
    "SET timezone" ought to do it ...
    I took Richs question to mean can you change the time portion
    supplied by Postgres, so:

    Instead of '2013-01-21' having the time portion set to local midnight
    it could be set to a user supplied value say, 08:00:00. That is not
    possible, correct. In the absence of a time portion a date string
    supplied to timestamp will always get local midnight?
    Thanks to all for the discussion of timestamps with/without timezones
    I have been learning a lot from the side.

    Taking another tangent I would much prefer the default time to be
    12:00:00 for the conversion of a date to timestamp(+/-timezone).

    Propose: '2013-12-25'::timestamp ==> 2013-12-25 12:00:00

    The benefit of the midday point is that the actual date will not
    change when going through the timezone conversion.
    Just like it doesn't change now? (I just checked against all of the more
    than 1,100 zones in PG without seeing a problem.)
    I see where my confusion lies. There are two proposals at work in the above:

    "Taking another tangent I would much prefer the default time to be
    12:00:00 for the conversion of a date to timestamp(+/-timezone)"

    "Propose: '2013-12-25'::timestamp ==> 2013-12-25 12:00:00 "

    For the timestamp(alias for timestamp without time zone) case the date
    does not change. For timestamp with time zone it might.
    Cheers,
    Steve


    --
    Adrian Klaver
    adrian.klaver@gmail.com
  • Gavan Schneider at Jan 22, 2013 at 4:56 am

    On Monday, January 21, 2013 at 10:53, Steve Crawford wrote:
    On 01/21/2013 02:48 PM, Gavan Schneider wrote:
    ....
    Taking another tangent I would much prefer the default time to
    be 12:00:00 for the conversion of a date to timestamp(+/-timezone).

    Propose: '2013-12-25'::timestamp ==> 2013-12-25 12:00:00

    The benefit of the midday point is that the actual date will
    not change when going through the timezone conversion.
    Just like it doesn't change now? (I just checked against all of the
    more than 1,100 zones in PG without seeing a problem.)
    I find this result strange to say the least... our conversation
    is straddling Monday(you)/Tuesday(me). We shared the time point
    2013-01-22 01:30 UTC, but called it different things, viz.,
    2013-01-22 12:30 and 2013-01-21 17:30.

    And any definition based on midnight(UTC) will cast to either
    side of the date line depending on the local timezone. This "is
    not a problem" per se. It just brings me back to my point that
    sometimes the date is more important than the notion of a point
    in time. Hence:
    This has implications for time-of-day insensitive data such as
    birthdays and other calendar values. I am still resolving "off
    by one day" errors that crept into many entries in my calendar
    and contacts from several years ago when data was added while
    travelling across multiple time zones (and I did report it as
    a bug back then). With this lesson learnt the workaround for
    me in my own applications since has been to store such dates
    as point-in-time for midday while keeping track of the
    input/output so it only gets used as a date... sometimes
    tedious, and a last resort. Mostly I have been actively
    avoiding anything with the taint of timezone due to this bad
    experience. It's time to reconsider, I guess, since this can
    cause other forms of silly behaviour.
    Date/time is not trivial. ...
    Total agreement here. And, as I said, I am going to school on
    this with a lot more insight after your's and other's input.
    ...
    Meanwhile if I'm up at that hour and try to schedule a job ...
    or possibly one of your machines is on the other side of the
    planet and running on tomorrow's time
    Aesthetically (and/or mathematically) the midday point is more
    accurate. It is the middle of the relevant interval (i.e., 24
    hours) implied by a date. Midnight is the extreme edge of any
    date (i.e., not what you would consider as mid-target).
    "Midnight" also has confusing English semantics since it can
    belong to either of its adjacent days.
    Except for days that are 23-hours long, or 25, or other (it's a big
    world with all sorts of timezone rules).
    The day's length may change but I don't believe there is
    anywhere that allows for the local time of day to equal or be
    greater than 24:00:00 without rolling over to the next day.

    How would that fit with ISO-8601?
          <http://en.wikipedia.org/wiki/ISO_8601#Times>
    It's also very useful for common queries (select ... from somelog
    where logtime > current_date) and provides a known starting-point from
    which you can easily calculate the offsets you desire.
    Agree, but aren't we better writing something like:
          SELECT ... FROM somelog WHERE logtime::date >= CURRENT_DATE;
    and not relying on an implementation detail for correct behaviour.

    Timestamps can always be busted back to lesser precision, i.e.,
    date only, but adding time information to a date is
    extrapolation. IMNSHO this sort of thing should be avoided.
    I don't know if the current behaviour will be deemed to be too
    rusted in place for change, or if this proposal has too many
    adverse consequences, but hope springs eternal. :)
    Obviously there is no discussion if current PostgreSQL behaviour
    is SQL standards compliant. I don't think anyone should ask that
    existing standards compliance be undone.
    It would sure break a lot of my queries. And for the many people who
    want/expect the date to cast to date at 00:00:00 local time it would
    lead to a load of pitfalls such as naively subtracting 12-hours or
    requiring the programmer to add complexity to determine how many hours
    to subtract based on local time zone and current date.
    This is assuming that someone would need to "correct" the hour
    when there was never any time of day information originally
    present. The naivety here is in attempting to correct something
    that is arbitrary. This is already a problem with the current
    system when attempting to "correct" times in all timezones,
    i.e., how many hours to add for a least wrong estimate of the time?
    But you are, of course, free to use the capability that PostgreSQL
    gives you to define pretty much any data-type you want along with your
    desired casting rules if you so desire. Just don't expect the built-in
    definitions to change.
    Thinking only, but it's way too early on my learning curve to
    venture there since such a data-type still has to play correctly
    with the rest of the system. And once I better know the system I
    may well have learnt to mitigate correctly in the relevant
    places. Mostly I avoid mixing timestamps with dates but figure I
    can't hide forever.
    On Monday, January 21, 2013 at 14:53, Adrian Klaver wrote:

    If I have learned anything about dealing with dates and times, is that
    it is a set of exceptions bound together by a few rules. Every time
    you think you have the little rascals cornered, one gets away.
    One more level of nesting and we have a quote of Churchillian
    scope. :)

    Regards
    Gavan Schneider
  • Nathan Clayton at Jan 22, 2013 at 7:13 am

    Monday, January 21, 2013, 8:56:38 PM, you wrote:

    Except for days that are 23-hours long, or 25, or other (it's a big
    world with all sorts of timezone rules).
    The day's length may change but I don't believe there is
    anywhere that allows for the local time of day to equal or be
    greater than 24:00:00 without rolling over to the next day.
    I only wish. I work with a transactional system from the 70s on
    a daily basis that decided to store something like a "work date" and
    "work time". The date changes whenever they decide to dateroll the
    system. Until then the time field continues to grow, so you see times
    like 25:00 and 26:00 all the time.

    Exceptions abound.

    --
      Nathan
  • Gavan Schneider at Jan 23, 2013 at 9:24 am

    On Monday, January 21, 2013 at 18:11, bgd39h5xxt@sneakemail.com (Nathan Clayton nathanclayton-at-gmail.com |pg-gts/Basic|) wrote:

    I only wish. I work with a transactional system from the 70s on
    a daily basis that decided to store something like a "work date" and
    "work time". The date changes whenever they decide to dateroll the
    system. Until then the time field continues to grow, so you see times
    like 25:00 and 26:00 all the time.
    SELECT execute(relevant_dba) FROM the_70s WITH tardis WHERE
    working_tardis = true;
    Exceptions abound.
    At least that can't be blamed on a government, and, we can only
    hope ISO-8601 will prevent more examples being created.

    You sound as though you really need, and/or already have, a
    dedicated datatype... if only to stop 'the system' from 'fixing'
    such weirdness.

    Regards
    Gavan Schneider
  • Steve Crawford at Jan 22, 2013 at 5:04 pm

    On 01/21/2013 08:56 PM, Gavan Schneider wrote:
    On Monday, January 21, 2013 at 10:53, Steve Crawford wrote:
    On 01/21/2013 02:48 PM, Gavan Schneider wrote:
    ....
    Taking another tangent I would much prefer the default time to be
    12:00:00 for the conversion of a date to timestamp(+/-timezone).

    Propose: '2013-12-25'::timestamp ==> 2013-12-25 12:00:00

    The benefit of the midday point is that the actual date will not
    change when going through the timezone conversion.
    Just like it doesn't change now? (I just checked against all of the
    more than 1,100 zones in PG without seeing a problem.)
    I find this result strange to say the least... our conversation is
    straddling Monday(you)/Tuesday(me). We shared the time point
    2013-01-22 01:30 UTC, but called it different things, viz., 2013-01-22
    12:30 and 2013-01-21 17:30.
    We can call it all sorts of things but it is, in fact, the same point in
    time. What you have done is omitted a critical piece of information
    necessary for a "fully qualified" point-in-time - the time zone.

    Now if I tell my wife I'll be home by 6 she says, "OK, see you then,"
    not "do you mean AM or PM? Er, Pacific time? Today?" In other words she
    makes reasonable assumptions about what point-in-time I am conveying.

    (Aside...It reminds me of the joke about the father admonishing his
    daughter's date to have her back by ten-fifteen to which he responds,
    "Mid-October? Cool!")

    A date alone can be interpreted as any of a number of points-in-time
    covering a roughly two-day range:

    select '2013-01-22 23:59 posix/Pacific/Midway'::timestamptz -
    '2013-01-22 00:00 Pacific/Kiritimati'::timestamptz;

    1 day 01:00:00

    So in order to calculate a single point-in-time, PostgreSQL, like my
    wife, has to make certain assumptions regarding the missing information
    (and fortunately PostgreSQL follows the SQL spec in this regard). The
    assumptions it makes are:

    1) Interpret the date in local time not the date somewhere else in the
    world.

    2) Interpret the missing time portion as 00:00:00.

    You now have a point-in-time, not a date. You can display that
    point-in-time in whatever timezone you wish and some will have the same
    date as your local date while others will not. Assuming that the time is
    12:00:00 rather than 00:00:00 does not change that fact:

    --localtime is US Pacific
    select '2013-01-22 12:00'::timestamptz at time zone 'Pacific/Kiritimati';

    2013-01-23 10:00:00

    Cheers,
    Steve
  • Jasen Betts at Jan 27, 2013 at 11:28 am

    On 2013-01-21, Steve Crawford wrote:
    Date/time is not trivial. The portions of the PostgreSQL manual dealing
    with those data types bear careful and thoughtful reading and rereading
    while you experiment at the same time in a psql terminal till it
    "clicks." And while some time issues are universal, treatment varies
    from program to program - especially regarding assumptions when the
    input is ambiguous. I'm in the US Pacific time zone so without further
    qualification, "2012-11-04 0130" could be 0130 PST or 0130 PDT.
    noveber suggests PST failry stongly.
    The "date" program on my Linux desktop assumes daylight time:
    date -d '2012-11-04 0130'
    Sun Nov 4 01:30:00 PDT 2012
    november is the DST changeover?
    PostgreSQL assumes standard time:
    select '2012-11-04 0130'::timestamptz;
    timestamptz
    ------------------------
    2012-11-04 01:30:00-08

    Naturally this can lead to all sorts of "fun" when multiple technologies
    are involved.

    Meanwhile if I'm up at that hour and try to schedule a job for immediate
    execution via "at now", the "at" program tells me it is "Cowardly
    refusing to schedule a job in the past." So much for even internal
    consistency.
    theres an hour in the night that I've learned to never schedlule cron
    jobs that must run atleast once or at most once.
    But you are, of course, free to use the capability that PostgreSQL gives
    you to define pretty much any data-type you want along with your desired
    casting rules if you so desire. Just don't expect the built-in
    definitions to change.
    --
    ⚂⚃ 100% natural
  • Gavan Schneider at Jan 22, 2013 at 12:20 am

    On Tuesday, January 22, 2013 at 09:48, I wrote:
    (and I did report it as a bug back then)
    Didn't pick this up on my pre-post re-read.... bug report was
    _NOT_ against PostgreSQL. It was some very early incarnations of
    OSX iCal, etc. which showed this behaviour.

    Apologies for the noise/confusion.

    Regards
    Gavan Schneider
  • Jasen Betts at Jan 27, 2013 at 10:58 am

    On 2013-01-21, Gavan Schneider wrote:
    On Monday, January 21, 2013 at 06:53, Adrian Klaver wrote:
    ....
    timezones I have been learning a lot from the side.

    Taking another tangent I would much prefer the default time to
    be 12:00:00 for the conversion of a date to timestamp(+/-timezone).

    Propose: '2013-12-25'::timestamp ==> 2013-12-25 12:00:00

    The benefit of the midday point is that the actual date will not
    change when going through the timezone conversion.
    that does not work, anywhere in the world, any time of day,it's always a different day somewhere
    (American Samoa vs Rarotonga be an extreme example, but one or the other will qualify if nowhere else does)

    If you want a date field use a date field, you can't reliably fake it using
    timestamptz


    --
    ⚂⚃ 100% natural
  • Adrian Klaver at Jan 21, 2013 at 7:54 pm

    On 01/21/2013 11:27 AM, Tom Lane wrote:
    Adrian Klaver <adrian.klaver@gmail.com> writes:
    On 01/21/2013 07:26 AM, Rich Shepard wrote:
    What is the behavior if a column data type is timestamptz but there is
    only the date portion available? There must be a default time; can that be
    defined?
    Easy enough to test:
    test=# create table ts_test(ts_fld timestamp with time zone);
    CREATE TABLE
    test=# insert into ts_test VALUES ('2013-01-21');
    INSERT 0 1
    test=# SELECT * from ts_test ;
    ts_fld
    ------------------------
    2013-01-21 00:00:00-08
    Note that that default is local midnight according to your current
    timezone setting (from which we may guess that Adrian lives on the US
    west coast, or somewhere in that general longitude).
    Not sure you can change the default supplied by Postgres,
    "SET timezone" ought to do it ...
    I took Richs question to mean can you change the time portion supplied
    by Postgres, so:

    Instead of '2013-01-21' having the time portion set to local midnight it
    could be set to a user supplied value say, 08:00:00. That is not
    possible, correct. In the absence of a time portion a date string
    supplied to timestamp will always get local midnight?
    regards, tom lane

    --
    Adrian Klaver
    adrian.klaver@gmail.com
  • Kevin Grittner at Jan 22, 2013 at 1:06 am

    Adrian Klaver wrote:

    I see where my confusion lies. There are two proposals at work in the above:

    "Taking another tangent I would much prefer the default time to be
    12:00:00 for the conversion of a date to timestamp(+/-timezone)"

    "Propose: '2013-12-25'::timestamp ==> 2013-12-25 12:00:00 "

    For the timestamp(alias for timestamp without time zone) case the date
    does not change. For timestamp with time zone it might.
    Well, the big problem here is in trying to use either version of
    timestamp when what you really want is a date. It will be much
    easier to get the right semantics if you use the date type for a
    date.

    -Kevin
  • Adrian Klaver at Jan 22, 2013 at 1:19 am

    On 01/21/2013 05:06 PM, Kevin Grittner wrote:
    Adrian Klaver wrote:
    I see where my confusion lies. There are two proposals at work in the above:

    "Taking another tangent I would much prefer the default time to be
    12:00:00 for the conversion of a date to timestamp(+/-timezone)"

    "Propose: '2013-12-25'::timestamp ==> 2013-12-25 12:00:00"

    For the timestamp(alias for timestamp without time zone) case the date
    does not change. For timestamp with time zone it might.
    Well, the big problem here is in trying to use either version of
    timestamp when what you really want is a date. It will be much
    easier to get the right semantics if you use the date type for a
    date.
    Agreed. If I was following Gavan correctly, he wanted to have a single
    timestamp field to store calender dates and datetimes. In other words to
    cover both date only situations like birthdays and datetime situations
    like an appointment.
    -Kevin

    --
    Adrian Klaver
    adrian.klaver@gmail.com
  • Gavan Schneider at Jan 22, 2013 at 3:40 am

    On Monday, January 21, 2013 at 12:06, Kevin Grittner wrote:
    Adrian Klaver wrote: [Actually Gavan Schneider wrote this, don't blame Adrian :]
    I see where my confusion lies. There are two proposals at work in the above:

    "Taking another tangent I would much prefer the default time
    to be 12:00:00 for the conversion of a date to timestamp(+/-timezone)"

    "Propose: '2013-12-25'::timestamp ==> 2013-12-25 12:00:00 "

    For the timestamp(alias for timestamp without time zone) case
    the date does not change. For timestamp with time zone it might.
    Well, the big problem here is in trying to use either version of
    timestamp when what you really want is a date. It will be much
    easier to get the right semantics if you use the date type for a
    date.
    This is the cleanest solution.

    And I did not want to imply the following...

    Adrian Klaver wrote:
    If I was following Gavan correctly, he wanted to have a single
    timestamp field to store calender dates and datetimes. In other
    words to cover both date only situations like birthdays and
    datetime situations like an appointment.
    My discussion really only applies to some notion of the best
    (or, more exactly, the least wrong) time to attribute to a date
    when conversion to timestamp happens for whatever reason. And,
    as indicated in my original post, I have been stung when dates
    got (badly) mixed into a datetime timezone aware context.

    The points raised by Adrain have prompted some more research on
    my part and I am intrigued to learn that on one day of the year
    in many countries (e.g., Brazil) where daylight conversion
    happens over midnight the local-time version of midnight as
    start of day does not exist. Basically the last day of
    unadjusted time ends at midnight and rolls directly into
    01:00:00 the next day (i.e., time 00:00:00 never happens on this
    one day). So the current date-> date+time system must already
    have some added complexity/overhead to check for this rare
    special case. (If not, there's a bug needs fixing!)

    Basically midnight is not safe as a target entity once timezones
    and daylight saving get involved. Midday, on the other hand, is
    a very solid proposition, no checks required, 12:00:00 will
    happen in all time zones on every day of the year! Basically
    nobody messes with their clocks in the middle of the day.

    So restating:
          '2013-10-20'::timestamp ==> 2013-10-20 12:00:00 can never
    be wrong; but,
          '2013-10-20'::timestamp ==> 2013-10-20 00:00:00 is wrong in
    some places.

    Regards
    Gavan Schneider
  • Adrian Klaver at Jan 22, 2013 at 3:53 am

    On 01/21/2013 07:40 PM, Gavan Schneider wrote:
    On Monday, January 21, 2013 at 12:06, Kevin Grittner wrote:


    Well, the big problem here is in trying to use either version of
    timestamp when what you really want is a date. It will be much
    easier to get the right semantics if you use the date type for a
    date.
    This is the cleanest solution.

    And I did not want to imply the following...
    Well, another fine assumption shot down:)
    Adrian Klaver wrote:
    If I was following Gavan correctly, he wanted to have a single
    timestamp field to store calender dates and datetimes. In other
    words to cover both date only situations like birthdays and
    datetime situations like an appointment.
    The points raised by Adrain have prompted some more research on my part
    and I am intrigued to learn that on one day of the year in many
    countries (e.g., Brazil) where daylight conversion happens over midnight
    the local-time version of midnight as start of day does not exist.
    Basically the last day of unadjusted time ends at midnight and rolls
    directly into 01:00:00 the next day (i.e., time 00:00:00 never happens
    on this one day). So the current date-> date+time system must already
    have some added complexity/overhead to check for this rare special case.
    (If not, there's a bug needs fixing!)
    If I have learned anything about dealing with dates and times, is that
    it is a set of exceptions bound together by a few rules. Every time you
    think you have the little rascals cornered, one gets away.

    Regards
    Gavan Schneider


    --
    Adrian Klaver
    adrian.klaver@gmail.com
  • Tom Lane at Jan 22, 2013 at 4:33 am

    Adrian Klaver writes:
    If I have learned anything about dealing with dates and times, is that
    it is a set of exceptions bound together by a few rules. Every time you
    think you have the little rascals cornered, one gets away.
    Yeah, that's for sure. Anyway, I think we are exceedingly unlikely to
    adopt Gavan's suggestion. It would break a huge amount of existing
    application code, and I think it is also arguably contrary to the SQL
    standard. The standard doesn't specify (at least, not that I've found)
    the external representation of datatype values; but it does specify what
    they're supposed to look like within literal constants in SQL commands.
    At least in SQL92 and SQL99 (too lazy to look at other versions right
    now), a timestamp literal that omits the time-of-day part is flat out
    illegal:

              <unquoted date string> ::= <date value>

              <unquoted time string> ::=
                   <time value> [ <time zone interval> ]

              <unquoted timestamp string> ::=
                   <unquoted date string> <space> <unquoted time string>

    Note the lack of square brackets there. The only way that you can
    really reconcile the spec with using just a <date value> in timestamp
    input is to suppose that the input is meant as a date and then we apply
    an implicit cast to timestamp. However, the spec definitely has an
    opinion on the meaning of such a cast. In 6.22 <cast specification>,
    SD and TD are the source and target datatypes for a cast, SV and TV are
    the source and target values:

             17) If TD is the datetime data type TIMESTAMP WITHOUT TIME ZONE,
                 then let TSP be the <timestamp precision> of TD.

                 b) If SD is a date, then the <primary datetime field>s hour,
                   minute, and second of TV are set to 0 (zero) and the <primary
                   datetime field>s year, month, and day of TV are set to their
                   respective values in SV.

             18) If TD is the datetime data type TIMESTAMP WITH TIME ZONE, then
                 let TSP be the <time precision> of TD.

                 b) If SD is a date, then TV is:

                      CAST (CAST (SV AS TIMESTAMP(TSP) WITHOUT TIME ZONE)
                      AS TIMESTAMP(TSP) WITH TIME ZONE)

                    (the behavior of that is defined as a timezone rotation)

    So it seems to me that the spec is pretty clearly on the side of filling
    in zeroes, ie local midnight.

    Now, you might say that there's an easy way around both the application
    breakage and the spec-compliance objections: let's just define a new GUC
    parameter that selects the behavior, with a backwards-compatible default
    setting. And ten years ago, I'd have probably said "hey, that's a great
    idea". But one of the things I've learned as the project goes along is
    that GUCs that affect application-visible semantics are dangerous
    things. Robust application code has to be made to cope with any
    possible setting of such a GUC, which makes them not nearly such a cheap
    fix as they seem initially. Especially not if the behavioral change is
    silent, with no possibility of detecting or reporting an error if the
    application is not expecting the new behavior.

        regards, tom lane
  • Gavan Schneider at Jan 22, 2013 at 6:33 am

    On Monday, January 21, 2013 at 15:33, Tom Lane wrote:
    I think it is also arguably contrary to the SQL standard...

    17) If TD is the datetime data type TIMESTAMP WITHOUT TIME ZONE,
    then let TSP be the <timestamp precision> of TD.

    b) If SD is a date, then the <primary datetime field>s hour,
    minute, and second of TV are set to 0 (zero) and the <primary
    datetime field>s year, month, and day of TV are set to their
    respective values in SV.
    That has to be the trump card.
    ... let's just define a new GUC parameter that selects the behavior,
    with a backwards-compatible default setting. ... Robust application
    code has to be made to cope with any possible setting of such a GUC,
    which makes them not nearly such a cheap fix as they seem
    initially. ...
    and, why go to significant trouble to implement standards
    non-compliance when there is no legacy code to support?

    I could always wish the SQL committee had thought along my lines
    all those years ago, and then again, I could just do something
    useful. :)

    On Monday, January 21, 2013 at 11:38, Adrian Klaver wrote:

    I must be missing something. I to am in PST:

    test=# \d ts_test
    Table "utility.ts_test"
    Column | Type | Modifiers
    --------+--------------------------+-----------
    ts_fld | timestamp with time zone |


    test=# INSERT INTO ts_test VALUES('2012-01-21');

    test=# SELECT * from ts_test ;
    ts_fld
    ------------------------
    2012-01-21 00:00:00-08

    test=# set timezone ='AKST9AKDT';

    test=# SELECT ts_fld from ts_test;
    ts_fld
    ------------------------
    2012-01-20 23:00:00-09
    The only thing missed is we are saying much same thing. There is
    no problem with the conversion. It is, as we see from Tom, fully
    SQL compliant. The only "problem" is when you are more
    interested in the date itself and not the point in time. This is
    just one of several scenarios where the date might get changed
    in ways that could be difficult to trace... caveat coder.


    Thanks again everyone for a lot more clarity in my thinking
    about dates times and timezones.

    Regards
    Gavan Schneider
  • Kevin Grittner at Jan 22, 2013 at 2:42 am

    Adrian Klaver wrote:

    If I was following Gavan correctly, he wanted to have a single
    timestamp field to store calender dates and datetimes. In other
    words to cover both date only situations like birthdays and
    datetime situations like an appointment.
    If that is actually true, it sounds like some reading on the
    benefits of normalizing to 3rd normal form is in order. What you
    describe is a violation of first normal form. Now, I recognize that
    most databases of any complexity need to denormalize to one degree
    or another for performance reasons; but I don't see the benefit of
    this particular type of denormalization.

    -Kevin
  • Gavan Schneider at Jan 22, 2013 at 5:30 pm

    On 01/21/2013 07:40 PM, Gavan Schneider wrote:
    ...
    The points raised by Adrain have prompted some more research on my
    part and I am intrigued to learn that on one day of the year in many
    countries (e.g., Brazil) where daylight conversion happens over
    midnight the local-time version of midnight as start of day does not
    exist. Basically the last day of unadjusted time ends at midnight and
    rolls directly into 01:00:00 the next day (i.e., time 00:00:00 never
    happens on this one day). So the current date-> date+time system must
    already have some added complexity/overhead to check for this rare
    special case. (If not, there's a bug needs fixing!)

    Basically midnight is not safe as a target entity once timezones and
    daylight saving get involved. Midday, on the other hand, is a very
    solid proposition, no checks required, 12:00:00 will happen in all
    time zones on every day of the year! Basically nobody messes with
    their clocks in the middle of the day.

    So restating:
    '2013-10-20'::timestamp ==> 2013-10-20 12:00:00 can never be
    wrong; but,
    '2013-10-20'::timestamp ==> 2013-10-20 00:00:00 is wrong in some
    places.
    "Wrong" times occur in every time zone that changes offsets at various
    points of the year. Here in California, 02:00:00-02:59:59 March 10, 2013
    are "wrong" but PostgreSQL uses a reasonable interpretation to yield a
    point-in-time:

    select '2013-03-10 0230'::timestamptz;
            timestamptz
    ------------------------
       2013-03-10 03:30:00-07

    And it does the exact same thing in Brazil:

    set timezone to 'Brazil/West';
    select '1993-10-17 00:00'::timestamptz;
            timestamptz
    ------------------------
       1993-10-17 01:00:00-03

    select '1993-10-17'::timestamptz;
            timestamptz
    ------------------------
       1993-10-17 01:00:00-03

    Note, too, that in both zones when the input is interpreted in the local
    zone and displayed in the local zone the date-portion of the
    point-in-time is the same as the input date. (While I suppose some
    politician somewhere could decide that "fall-back" could cross date
    boundaries, I am unaware of any place that has ever done something so
    pathological as to have the same date occur in two non-contiguous pieces
    once every year.)

    Cheers,
    Steve
  • Gavin Flower at Jan 22, 2013 at 5:37 pm

    On 23/01/13 06:30, Gavan Schneider wrote:
    On 01/21/2013 07:40 PM, Gavan Schneider wrote: [...]
    (While I suppose some politician somewhere could decide that
    "fall-back" could cross date boundaries, I am unaware of any place
    that has ever done something so pathological as to have the same date
    occur in two non-contiguous pieces once every year.)
    [...]

    Don't tempt the gods!!! :-)


    Cheers,
    Gavin
  • Steve Crawford at Jan 22, 2013 at 5:42 pm

    On 01/22/2013 09:37 AM, Gavin Flower wrote:
    On 23/01/13 06:30, Gavan Schneider wrote:
    On 01/21/2013 07:40 PM, Steve Crawford wrote: [...]
    (While I suppose some politician somewhere could decide that
    "fall-back" could cross date boundaries, I am unaware of any place
    that has ever done something so pathological as to have the same date
    occur in two non-contiguous pieces once every year.)
    [...]

    Don't tempt the gods!!! :-)
    Sorry. :)

    Cheers,
    Steve
  • Jasen Betts at Jan 27, 2013 at 10:29 am

    On 2013-01-21, Rich Shepard wrote:
    What is the behavior if a column data type is timestamptz but there is
    only the date portion available? There must be a default time; can that be
    defined?
    No, if you don't specify the time 00:00 (midnight) is used.
    if you don't specify a timezone it's local midnight, so viewed from
    a neighbouring timezone it could be 1AM or 11pm the previous day.


    --
    ⚂⚃ 100% natural

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJan 21, '13 at 3:26p
activeJan 27, '13 at 11:28a
posts29
users9
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase