Re: Order by and timestamp - Mailing list pgsql-general
| From | Björn Lundin |
|---|---|
| Subject | Re: Order by and timestamp |
| Date | |
| Msg-id | 48A75793-3914-40F5-877C-FD87AE85AE8A@gmail.com Whole thread Raw |
| In response to | Re: Order by and timestamp (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Responses |
Re: Order by and timestamp
|
| List | pgsql-general |
16 mars 2020 kl. 01:37 skrev Adrian Klaver <adrian.klaver@aklaver.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@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@ibm2:~$ \c dry
-bash: c: kommandot finns inte
bnl@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
pgsql-general by date: