Re: Order by and timestamp

On 3/16/20 2:28 PM, Björn Lundin wrote:
>
>
>> 16 mars 2020 kl. 17:40 skrev Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
>> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>>:
>>
>> =?utf-8?Q?Bj=C3=B6rn_Lundin?= <b(dot)f(dot)lundin(at)gmail(dot)com
>> <mailto:b(dot)f(dot)lundin(at)gmail(dot)com>> 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

A quick look at the dump data in your other post indicates the data
values themselves are not bad, just that sorting is not correct.

I do not have a 9.4 instance available, so I cannot test below.

>
>
>
> 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(at)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(at)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(at)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(at)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(at)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(at)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(at)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(dot)f(dot)lundin(at)gmail(dot)com <mailto:b(dot)f(dot)lundin(at)gmail(dot)com>
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com