Re: Order by and timestamp

On Sun, 15 Mar 2020 22:33:35 +0100:wq
Björn Lundin <b(dot)f(dot)lundin(at)gmail(dot)com> wrote:

> And to my surprise i get a result like this (note the order of
> column STARTTS)

(1) Suggest using "pastebin.com" for this kind of data. It may not
look very pretty -- or readable at all -- on the viewer's end
depending on their settings (see below for example).

(2) I think you are refering to one section where the date goes
from 2016-10-01 to 2016-09-30; suggest describing the
transition in your text and flag the rows with '*' or
something similar.

| 2016-10-01 15:35:00 |
| 2016-10-01 16:10:00 |
* | 2016-09-30 13:00:00 |
* | 2016-09-30 13:00:00 |

(3) "Old database" might mean anyting. Provide the PG version
it was created in and the one you are using along with the
result of "\d+" in the current database.

(4) Classic causes of this are a botched index. Depending on the
size you might just want to either drop and re-add the
indexes or export and reload the table (e.g., \copy to ...
+ truncate + \copy from ...). The point there would be
fully rebuilding the table and index structure.

If that doesn't work perhaps drop and re-add the table with
whatever version of PG you are using and then \copy the data
back in using the current version.

(5) If you've tried any of the above then bloody well describe it
(along with any migration steps taken) in the message so you
don't have to re-read what you've already done :-)

(6) Don't gamble on horses, play the stock market instead: It
sounds fancier and you can loose much more money much more
quickly... er... yeah.

What this looks like on my end. Feel free to try and make sense
of it yourself.

> marketid | marketname | startts |
> eventid ….
> ….
>
> 1.127253880 | To Be Placed | 2016-09-29 16:10:00 |
> 27951325 | 1.127275624 | 1m4f Hcap | 2016-09-30
> 16:20:00 | 27953169 | 1.127275625 | To Be Placed |
> 2016-09-30 16:20:00 | 27953169 | 1.127275629 | 1m2f
> Hcap | 2016-09-30 16:50:00 | 27953169 | 1.127275634 |
> 1m2f Hcap | 2016-09-30 17:20:00 | 27953169 |
> 1.127275635 | To Be Placed | 2016-09-30 17:20:00 |
> 27953169 | 1.127275639 | 1m Nursery | 2016-09-30
> 17:50:00 | 27953169 | 1.127275640 | To Be Placed |
> 2016-09-30 17:50:00 | 27953169 | 1.127275645 | To Be
> Placed | 2016-09-30 18:20:00 | 27953169 | 1.127275649 |
> 6f Mdn Stks | 2016-09-30 18:50:00 | 27953169 |
> 1.127275650 | To Be Placed | 2016-09-30 18:50:00 |
> 27953169 | 1.127275654 | 5f Hcap | 2016-09-30
> 19:20:00 | 27953169 | 1.127275655 | To Be Placed |
> 2016-09-30 19:20:00 | 27953169 | 1.127275659 | 5f
> Hcap | 2016-09-30 19:50:00 | 27953169 | 1.127275660
> | To Be Placed | 2016-09-30 19:50:00 | 27953169 |
> 1.127275677 | 1m Mdn Stks | 2016-10-01 12:45:00 |
> 27953172 | 1.127275680 | To Be Placed | 2016-10-01
> 12:45:00 | 27953172 | 1.127275684 | 6f Hcap |
> 2016-10-01 13:15:00 | 27953172 | 1.127275687 | To Be
> Placed | 2016-10-01 13:15:00 | 27953172 | 1.127275691 |
> 1m Hcap | 2016-10-01 13:50:00 | 27953172 |
> 1.127275694 | To Be Placed | 2016-10-01 13:50:00 |
> 27953172 | 1.127275698 | 1m2f Hcap | 2016-10-01
> 14:25:00 | 27953172 | 1.127275701 | To Be Placed |
> 2016-10-01 14:25:00 | 27953172 | 1.127275705 | 1m
> Grp1 | 2016-10-01 15:00:00 | 27953172 | 1.127275708
> | To Be Placed | 2016-10-01 15:00:00 | 27953172 |
> 1.127275715 | To Be Placed | 2016-10-01 15:35:00 |
> 27953172 | 1.127275722 | To Be Placed | 2016-10-01
> 16:10:00 | 27953172 | 1.127278857 | 7f Hcap |
> 2016-09-30 13:00:00 | 27953255 | 1.127278858 | To Be
> Placed | 2016-09-30 13:00:00 | 27953255 | 1.127278862 |
> 1m Class Stks | 2016-09-30 13:35:00 | 27953255 |
> 1.127278863 | To Be Placed | 2016-09-30 13:35:00 |
> 27953255 | 1.127278867 | 6f Hcap | 2016-09-30
> 14:10:00 | 27953255 | … ….
>
> 1.130630452 | 2m INHF | 2017-03-30 16:00:00 |
> 28172518 | 1.130630453 | To Be Placed | 2017-03-30
> 16:00:00 | 28172518 | 1.130645203 | 1m2f Mdn Stks |
> 2017-04-01 12:30:00 | 28173548 | 1.130645204 | To Be
> Placed | 2017-04-01 12:30:00 | 28173548 | 1.130645213 |
> 6f Hcap | 2017-04-01 13:40:00 | 28173548 |
> 1.130645214 | To Be Placed | 2017-04-01 13:40:00 |
> 28173548 | 1.130645218 | 1m3f Hcap | 2017-04-01
> 14:15:00 | 28173548 | 1.130645219 | To Be Placed |
> 2017-04-01 14:15:00 | 28173548 | 1.130645223 | 7f Mdn
> Stks | 2017-04-01 14:50:00 | 28173548 | 1.130645224 | To
> Be Placed | 2017-04-01 14:50:00 | 28173548 | 1.130645228
> | 1m3f Hcap | 2017-04-01 15:25:00 | 28173548 |
> 1.130645229 | To Be Placed | 2017-04-01 15:25:00 |
> 28173548 | 1.130645233 | 2m Hcap | 2017-04-01
> 16:00:00 | 28173548 | 1.130645234 | To Be Placed |
> 2017-04-01 16:00:00 | 28173548 | 1.130645400 | 2m3f Nov
> Hrd | 2017-03-31 13:10:00 | 28173582 | 1.130645401 | To
> Be Placed | 2017-03-31 13:10:00 | 28173582 | 1.130645405
> | 2m5f Hcap Chs | 2017-03-31 13:40:00 | 28173582 |
> 1.130645415 | 2m1f Hcap Chs | 2017-03-31 14:40:00 |
> 28173582 | 1.130645416 | To Be Placed | 2017-03-31
> 14:40:00 | 28173582 | 1.130645420 | 2m5f Hcap Hrd |
> 2017-03-31 15:10:00 | 28173582 | 1.130645421 | To Be
> Placed | 2017-03-31 15:10:00 | 28173582 | 1.130645425 |
> 2m3f Hcap Chs | 2017-03-31 15:40:00 | 28173582 |
> 1.130645426 | To Be Placed | 2017-03-31 15:40:00 |
> 28173582 | 1.130645430 | 1m5f Stks NHF | 2017-03-31
> 16:10:00 | 28173582 | 1.130645431 | To Be Placed |
> 2017-03-31 16:10:00 | 28173582 | 1.130645436 | 1m4f
> Hcap | 2017-03-31 16:45:00 | 28173583 | 1.130645437 |
> To Be Placed | 2017-03-31 16:45:00 | 28173583 |
> 1.130645441 | 1m Hcap | 2017-03-31 17:15:00 |
> 28173583 | 1.130645442 | To Be Placed | 2017-03-31
> 17:15:00 | 28173583 | 1.130645447 | To Be Placed |
> 2017-03-31 17:45:00 | 28173583 | 1.130645451 | 7f
> Hcap | 2017-03-31 18:15:00 | 28173583 | 1.130645452
> | To Be Placed | 2017-03-31 18:15:00 | 28173583 |
> 1.130645456 | 1m Hcap | 2017-03-31 18:45:00 |
> 28173583 | 1.130645457 | To Be Placed | 2017-03-31
> 18:45:00 | 28173583 | 1.130645461 | 5f Hcap |
> 2017-03-31 19:15:00 | 28173583 | 1.130645462 | To Be
> Placed | 2017-03-31 19:15:00 | 28173583 | 1.130645466 |
> 5f Hcap | 2017-03-31 19:45:00 | 28173583 |
> 1.130645471 | 1m Hcap | 2017-03-31 13:00:00 |
> 28173584 | 1.130645472 | To Be Placed | 2017-03-31
> 13:00:00 | 28173584 | 1.130645476 | 6f Hcap |
> 2017-03-31 13:30:00 | 28173584 | 1.130645477 | To Be
> Placed | 2017-03-31 13:30:00 | 28173584 | 1.130645481 |
> 1m5f Stks | 2017-03-31 14:00:00 | 28173584 |
> 1.130645482 | To Be Placed | 2017-03-31 14:00:00 |
> 28173584 | 1.130645486 | 5f Hcap | 2017-03-31
> 14:30:00 | 28173584 | 1.130645487 | To Be Placed |
> 2017-03-31 14:30:00 | 28173584 | 1.130645491 | 2m
> Hcap | 2017-03-31 15:00:00 | 28173584 | 1.130645492
> | To Be Placed | 2017-03-31 15:00:00 | 28173584 |
> 1.130645496 | 6f Hcap | 2017-03-31 15:30:00 |
> 28173584 | 1.130645497 | To Be Placed | 2017-03-31
> 15:30:00 | 28173584 | 1.130645501 | 1m2f Mdn Stks |
> 2017-03-31 16:00:00 | 28173584 | 1.130645502 | To Be
> Placed | 2017-03-31 16:00:00 | 28173584 | 1.130645507 |
> 2m Nov Hrd | 2017-03-31 13:20:00 | 28173585 |
> 1.130645508 | To Be Placed | 2017-03-31 13:20:00 |
> 28173585 | 1.130645512 | 1m7f Hcap Chs | 2017-03-31
> 13:50:00 | 28173585 | 1.130645513 | To Be Placed |
> 2017-03-31 13:50:00 | 28173585 | 1.130645517 | 2m5f Hcap
> Hrd | 2017-03-31 14:20:00 | 28173585 | 1.130645518 | To Be
> Placed | 2017-03-31 14:20:00 | 28173585 | 1.130645801 |
> To Be Placed | 2017-03-31 20:00:00 | 28173591 |
> 1.130662612 | 6f Listed Stks | 2017-04-01 12:50:00 |
> 28174115 | 1.130662613 | To Be Placed | 2017-04-01
> 12:50:00 | 28174115 | 1.130662617 | 1m Hcap |
> 2017-04-01 13:25:00 | 28174115 | 1.130662618 | To Be
> Placed | 2017-04-01 13:25:00 | 28174115 | 1.130662622 |
> 1m Listed Stks | 2017-04-01 14:00:00 | 28174115 |
> 1.130662623 | To Be Placed | 2017-04-01 14:00:00 |
> 28174115 | 1.130662627 | 1m Hcap | 2017-04-01
> 14:35:00 | 28174115 | 1.130662628 | To Be Placed |
> 2017-04-01 14:35:00 | 28174115 | 1.130662632 | 5f Cond
> Stks | 2017-04-01 15:10:00 | 28174115 | 1.130662633 | To
> Be Placed | 2017-04-01 15:10:00 | 28174115 | 1.130662637
> | 5f Cond Stks | 2017-04-01 15:45:00 | 28174115 |
> 1.130662638 | To Be Placed | 2017-04-01 15:45:00 |
> 28174115 | 1.130662643 | To Be Placed | 2017-04-01
> 16:20:00 | 28174115 | 1.130662647 | 1m2f Hcap |
> 2017-04-01 16:50:00 | 28174115 |
>
>
> I see this on some dates, but most are in order
> Actually it looks like ’order by MARKETID'
>
> The data is collected on Amazon cloud, Ireland, and in Sweden. time
> diff is 1 hour between the countries, GMT / CET
>
> Hmm, I now realise that daylight saving time starts stops
> ended 2016-10-30, and started again 2017-03-26
> so it is not on the bad dates.
>
> I vaguely recall that I have once defined the column as
> Timestamp with timezone, and changed it to without.
> May that have an impact?
>
> I realize that I should migrate to 11 or 12, but hmm, I still wonder
> over this sort order.
>
> TZ=Europe/Stockholm
>
>
> Timing is on.
> AUTOCOMMIT off
> psql (9.6.10)
> Type "help" for help.
>
> bnl=# \d amarkets
> Table "public.amarkets"
> Column | Type |
> Modifiers
> ------------------+--------------------------------+-----------------------------------------
> marketid | character varying(11) | not null default
> ' '::character varying marketname | character
> varying(50) | not null default ' '::character varying
> startts | timestamp(3) without time zone | not null
> eventid | character varying(11) | not null default
> ' '::character varying markettype | character
> varying(25) | not null default ' '::character varying
> status | character varying(50) | not null default
> ' '::character varying betdelay |
> integer | not null default 1 numwinners
> | integer | not null default 1
> numrunners | integer | not null default
> 1 numactiverunners | integer | not null
> default 1 totalmatched | numeric(15,2) | not
> null default 0.0 totalavailable | numeric(15,2) |
> not null default 0.0 ixxlupd | character
> varying(15) | not null default ' '::character varying
> ixxluts | timestamp(3) without time zone | not null Indexes:
> "amarketsp1" PRIMARY KEY, btree (marketid) "amarketsi2" btree
> (eventid) "amarketsi3" btree (markettype) "amarketsi4" btree (status)
> "amarketsi5" btree (numwinners) "amarketsi6" btree (ixxluts)
>
> bnl=#
>
>
> regards
> --
> Björn Lundin
> b(dot)f(dot)lundin(at)gmail(dot)com

--
Steven Lembark 3646 Flora Place
Workhorse Computing St. Louis, MO 63110
lembark(at)wrkhors(dot)com +1 888 359 3508