Re: Order by and timestamp

On Sun, 15 Mar 2020 22:33:35 +0100:wq
Björn Lundin <b.f.lundin@gmail.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.f.lundin@gmail.com


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