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(dot)Elphick(at)lfix(dot)co(dot)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