Re: Order by and timestamp

> 16 mars 2020 kl. 01:37 skrev Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:
>
> On 3/15/20 2:33 PM, Björn Lundin wrote:
>> 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’
>
> Is amarkets in more then one schema?

Yes but the table is empty in other schema (’dry’) - and has less idexes
It is also present in imports - but empty there as well

bnl(at)ibm2:~$ psql -l
Tidtagning är på.
AUTOCOMMIT off
Lista med databaser
Namn | Ägare | Kodning | Jämförelse | Ctype | Åtkomsträttigheter
-----------+----------+---------+-------------+-------------+-----------------------
bnl | bnl | UTF8 | sv_SE.UTF-8 | sv_SE.UTF-8 |
dry | bnl | UTF8 | sv_SE.UTF-8 | sv_SE.UTF-8 |
imports | bnl | UTF8 | sv_SE.UTF-8 | sv_SE.UTF-8 |
postgres | postgres | UTF8 | sv_SE.UTF-8 | sv_SE.UTF-8 |
template0 | postgres | UTF8 | sv_SE.UTF-8 | sv_SE.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | sv_SE.UTF-8 | sv_SE.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(6 rader)

bnl(at)ibm2:~$ \c dry
-bash: c: kommandot finns inte
bnl(at)ibm2:~$ psql
Tidtagning är på.
AUTOCOMMIT off
psql (9.6.15, server 9.4.15)
Skriv "help" för hjälp.

bnl=> \c dry
psql (9.6.15, server 9.4.15)
Du är nu uppkopplad mot databasen "dry" som användare "bnl".
dry=> \d amarkets
Tabell "public.amarkets"
Kolumn | Typ | Modifierare
------------------+--------------------------------+------------------------------------------
marketid | character varying(11) | inte null default ' '::character varying
marketname | character varying(50) | inte null default ' '::character varying
startts | timestamp(3) without time zone | inte null
eventid | character varying(11) | inte null default ' '::character varying
markettype | character varying(25) | inte null default ' '::character varying
status | character varying(50) | inte null default ' '::character varying
betdelay | integer | inte null default 1
numwinners | integer | inte null default 1
numrunners | integer | inte null default 1
numactiverunners | integer | inte null default 1
totalmatched | numeric(15,2) | inte null default 0.0
totalavailable | numeric(15,2) | inte null default 0.0
ixxlupd | character varying(15) | inte null default ' '::character varying
ixxluts | timestamp(3) without time zone | inte null
Index:
"amarketsp1" PRIMARY KEY, btree (marketid)

dry=> select count('a') from amarkets;
count
-------
0
(1 rad)

Tid: 2,059 ms
dry=>

dry=> \c imports
psql (9.6.15, server 9.4.15)
Du är nu uppkopplad mot databasen "imports" som användare "bnl".
imports=> \d
Lista med relationer
Schema | Namn | Typ | Ägare
--------+--------------------+--------+-------
public | abets | tabell | bnl
public | aevents | tabell | bnl
public | aevents_tmp | tabell | bnl
public | amarkets | tabell | bnl
public | amarkets_tmp | tabell | bnl
public | aprices | tabell | bnl
public | aprices_tmp | tabell | bnl
public | apriceshistory | tabell | bnl
public | apriceshistory_tmp | tabell | bnl
public | arunners | tabell | bnl
public | arunners_tmp | tabell | bnl
(11 rader)

imports=> select count('a') from amarkets;
count
-------
0
(1 rad)

> If so what is search_path?

bnl=> show search_path;
search_path
----------------
"$user",public
(1 rad)

> I could not replicate the below.
>
> What does below show?:
>
> select '2016-09-30 13:00:00'::timestamp at time zone 'UTC’;

bnl=> select '2016-09-30 13:00:00'::timestamp at time zone 'UTC';
timezone
------------------------
2016-09-30 15:00:00+02

> select '2016-10-01 15:35:00'::timestamp at time zone ’UTC’
bnl=> select '2016-10-01 15:35:00'::timestamp at time zone 'UTC';
timezone
------------------------
2016-10-01 17:35:00+02

--
Björn Lundin
b(dot)f(dot)lundin(at)gmail(dot)com