Re: Date formats

On Mon, 2003-05-26 at 18:20, Michelle Murrain wrote:
> In doing an upgrade (I'm using Debian testing, and a recent
> dist-upgrade included an upgrade to 7.3.2), I must have messed up the
> date/time format - because one table did not automatically restore,
> and in trying to restore that table from my own backup, it is giving
> me the error "Bad Timestamp external representation"
>
> I thought I was being careful to pick the right formats during the
> upgrade,  but obviously I screwed up.
>
> How can I fix this (besides writing a script to just insert the
> records sans timestamp or converting the timestamp)?
>
> 1) the old timestamp format is MM/DD/YYYY hh:mm:ss
>
> How can I find out what the new format is? Can I convert it to the
> old (by doing an new initdb?)

That format is OK, provided that you have the correct datestyle set:

        junk=# show datestyle;
                   DateStyle
        -------------------------------
         ISO with European conventions
        (1 row)

        junk=# select '12/13/2003 13:43:23'::timestamp;
        ERROR:  Bad timestamp external representation '12/13/2003 13:43:23'
        junk=# set datestyle to US;
        SET
        junk=# show datestyle;
                       DateStyle
        ---------------------------------------
         ISO with US (NonEuropean) conventions
        (1 row)

        junk=# select '12/13/2003 13:43:23'::timestamp;
              timestamp
        ---------------------
         2003-12-13 13:43:23
        (1 row)

The problem is probably that you have European conventions set, and it
is confusing month and day.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Thou will show me the path of life; in thy presence
      is fullness of joy; at thy right hand there are
      pleasures for evermore."         Psalms 16:11