Search Postgresql Archives

Order by and timestamp

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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=# 


regards
--
Björn Lundin
b.f.lundin@xxxxxxxxx




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux