Hi! I have an old database that behaves a bit strange. I keeps horse races in UK/IE. I have a program that continuously* adds record into a market table , described as below. *continuously means ’after each race’ which is ca 12:00 --> 23:00. I then did ’select * from AMARKETS order by STARTTS’ And to my surprise i get a result like this (note the order of column STARTTS) 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=# |
