Re: Order by and timestamp

[Björn Lundin <b.f.lundin@xxxxxxxxx>]

16 mars 2020 kl. 17:40 skrev Tom Lane <tgl@xxxxxxxxxxxxx>:

=?utf-8?Q?Bj=C3=B6rn_Lundin?= <b.f.lundin@xxxxxxxxx> writes:

Ooh - terrible sorry.
The output from first post describing the database schema
Was actually from my production machine - a raspberry pi.
The pi hold a db on an usb-disk, which is pg_dump()ed every night and imported to ibm2 history db (the bad one)

The schema is identical to the one with trouble - which is a history database
Intended for testing

Hmmm ... schema is evidently the same, but locale seemingly not.
Is it possible this is a locale problem?  I did not look closely
at the original data, but I'm suddenly wondering if it could be
explained by misinterpreting the date field order (month-day vs
day-month).  Check the datestyle settings on both machines.

regards, tom lane

But most of the output is correct. Like 95+% 

I found the bad ones by chance

The pi - keeping only data for 1 day then table exported and truncated every night 

(So there is not more than say max 60 records any given day)

Was only involved because I use it to describe the table

bnl@pibetbot:~ $ locale

LANG=en_GB.UTF-8

LANGUAGE=

LC_CTYPE="en_GB.UTF-8"

LC_NUMERIC="en_GB.UTF-8"

LC_TIME="en_GB.UTF-8"

LC_COLLATE="en_GB.UTF-8"

LC_MONETARY="en_GB.UTF-8"

LC_MESSAGES="en_GB.UTF-8"

LC_PAPER="en_GB.UTF-8"

LC_NAME="en_GB.UTF-8"

LC_ADDRESS="en_GB.UTF-8"

LC_TELEPHONE="en_GB.UTF-8"

LC_MEASUREMENT="en_GB.UTF-8"

LC_IDENTIFICATION="en_GB.UTF-8"

LC_ALL=

bnl@pibetbot:~ $ psql

Timing is on.

AUTOCOMMIT off

psql (9.6.10)

Type "help" for help.

bnl=# show datestyle;

 DateStyle 

-----------

 ISO, DMY

The faulty machine 

Ibm2 - linux debian pg-9.4

bnl@ibm2:~$ locale

LANG=sv_SE.UTF-8

LANGUAGE=

LC_CTYPE="sv_SE.UTF-8"

LC_NUMERIC="sv_SE.UTF-8"

LC_TIME="sv_SE.UTF-8"

LC_COLLATE="sv_SE.UTF-8"

LC_MONETARY="sv_SE.UTF-8"

LC_MESSAGES="sv_SE.UTF-8"

LC_PAPER="sv_SE.UTF-8"

LC_NAME="sv_SE.UTF-8"

LC_ADDRESS="sv_SE.UTF-8"

LC_TELEPHONE="sv_SE.UTF-8"

LC_MEASUREMENT="sv_SE.UTF-8"

LC_IDENTIFICATION="sv_SE.UTF-8"

LC_ALL=

bnl@ibm2:~$ psql

Tidtagning är på.

AUTOCOMMIT off

psql (9.6.15, server 9.4.15)

Skriv "help" för hjälp.

bnl=> show datestyle;

 DateStyle 

-----------

 ISO, YMD

The machine briefly mentioned with basically same dataset as faulty machine

Linux Ubuntu with pg-10.6 

bnl@tp:~$ locale

LANG=sv_SE.UTF-8

LANGUAGE=

LC_CTYPE="sv_SE.UTF-8"

LC_NUMERIC="sv_SE.UTF-8"

LC_TIME="sv_SE.UTF-8"

LC_COLLATE="sv_SE.UTF-8"

LC_MONETARY="sv_SE.UTF-8"

LC_MESSAGES="sv_SE.UTF-8"

LC_PAPER="sv_SE.UTF-8"

LC_NAME="sv_SE.UTF-8"

LC_ADDRESS="sv_SE.UTF-8"

LC_TELEPHONE="sv_SE.UTF-8"

LC_MEASUREMENT="sv_SE.UTF-8"

LC_IDENTIFICATION="sv_SE.UTF-8"

LC_ALL=

bnl@tp:~$ psql

Timing is on.

AUTOCOMMIT off

psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1))

Type "help" for help.

bnl=# show datestyle;

 DateStyle 

-----------

 ISO, YMD

Both faulty (ibm2) and correct(tp) are populated with the same pg_dump()- files that r-pi produces every nigth

And for completeness - b info from the pi

bnl=# select version();

                                                       version                                                        

----------------------------------------------------------------------------------------------------------------------

 PostgreSQL 9.6.10 on armv7l-unknown-linux-gnueabihf, compiled by gcc (Raspbian 6.3.0-18+rpi1) 6.3.0 20170516, 32-bit

(1 row)

bnl@pibetbot:~ $ uname -a

Linux pibetbot 4.14.79-v7+ #1159 SMP Sun Nov 4 17:50:20 GMT 2018 armv7l GNU/Linux

--

Björn Lundin

b.f.lundin@xxxxxxxxx