Order by and timestamp
| От | Björn Lundin |
|---|---|
| Тема | Order by and timestamp |
| Дата | |
| Msg-id | 63004F55-4CA3-40F8-A898-E7A646B0EBD3@gmail.com обсуждение исходный текст |
| Ответы |
Re: Order by and timestamp
Re: Order by and timestamp |
| Список | pgsql-general |
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=#
В списке pgsql-general по дате отправления:
Предыдущее
От: Karsten HilbertДата:
Сообщение: Re: unexpected chunk number 2 (expected 0) for toast value ... inpg_toast_18536
Следующее
От: Adrian KlaverДата:
Сообщение: Re: unexpected chunk number 2 (expected 0) for toast value ... inpg_toast_18536