Re: date with month and year

Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx> · Thu, 21 May 2015 11:56:52 -0700



Title: Konsole output

  
    On 05/21/2015 10:45 AM, Paul Jungwirth
      wrote:

    
      You really shouldn't use WITHOUT TIME
        ZONE.
        

      I'd like to know more about this. Can you say why?

    
    Start by reading about the date and time data types with special
    attention to section 8.5.3:

    www.postgresql.org/docs/current/static/datatype-datetime.html

    
    Now go back and read it again and experiment a while until it makes
    sense. As Adrian Klaver so eloquently put it, "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." This is also a
    very good reason to avoid reinventing the wheel.

    
    When you need a break, watch this:

    https://www.youtube.com/watch?v=-5wpm-gesOY

    
    His conclusion is a good one: be very happy that someone else has
    done the dirty work for you.

    
    The Ruby article does make one good point which is that we are
    talking about what they call an "instant" or what I like to refer to
    as a "point in time." The "point in time" is actually a better way
    of thinking of "timestamp with time zone" since the "timestamp with
    time zone" does not actually store any timezone information - it
    stores a point in time that can be manipulated in the time-zone of
    your choosing whereas timestamp without time zone is not a point in
    time and must be combined with other information to do proper
    manipulation.

    
    The article does also display a couple attitudes that I feel are
    especially rampant in the web-development community. The first is
    that web developers shouldn't become educated about the capabilities
    of a database but rather use the database as a dumb data-store and
    redo everything themselves (often this includes an utter failure to
    use the data-integrity capabilities of the database).

    
    The second is the assumption that they are the only users of the
    database and that nobody will ever access the data except through
    their custom-written Ruby/PHP/Perl/Python code and that no other
    programming language will ever be used. Woe be to the poor slob who
    has to deal with ad-hoc queries, analytics platforms or reporting
    systems that weren't so brilliantly reinvented or who wants to use
    range-types or other nice PostgreSQL features.

    
    Internally PostgreSQL stores timestamp without time zone in UTC but
    that is entirely irrelevant. What is relevant is that you can
    provide an "instant"/"point in time" in whatever time-zone
    representation you want and get it back the same way. Want to use a
    Unix epoch in your code. Go ahead:

    extract(epoch from yourtstzcol)

    abstime(yourepochint)

    
    Want to assume everything is UTC? No problem:

    
      set timezone to 'UTC';

      
      Then you can reinvent wheels to your heart's content without
      wrecking the ability to easily use other tools.

      
    By the way, use full timezone names to avoid ambiguity. I don't know
    what Ruby cooked up but PostgreSQL uses industry-standard names:

    Konsole output
    
      select * from pg_timezone_names;

    
    Your original question had to do with month/year. You will have to
    define this for your use-case but beware that it won't necessarily
    get you away from time-zone issues as the month ticks over on a
    zone-by-zone basis.

    
    Also note that time-intervals can be a source of interesting
    side-effects. Operator precedence is important. For example, what is
    one month? 28-days? 29? 30? 31? Every system must make a judgment
    call. Add a month to January 31 and you will get February 28. But
    add/subtract a month from February 28 and you get January 28/March
    28. So you can create a query that takes a date, adds a month and
    subtracts a month and results in a different date. There is nothing
    to do here but to read the docs and try things.

    
    There are similar issues when crossing DST boundaries. If I want to
    push something out a day in my time-zone on the day that DST changes
    I can do it easily and understand that PostgreSQL will handle the
    extra/missing hour. Or I can use an explicit increment of '24 hours'
    if that is what I want. No extra steps of converting the timestamp
    without time zone to UTC, converting that to the desired local zone,
    doing the calculations, converting back to UTC and back to timezone
    without timestamp all the while potentially adding an easy error
    such as doing things in the wrong order and checking for DST
    changeover in the wrong time-zone.

    
    Cheers,

    Steve