Re: Order by and timestamp - Mailing list pgsql-general

On 3/16/20 1:49 AM, Björn Lundin wrote:
> 
> 
>> 16 mars 2020 kl. 01:37 skrev Adrian Klaver <adrian.klaver@aklaver.com 
>> <mailto: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

Actually the below indicates it is in other databases. A schema would be 
a namespace within a database, see here:

https://www.postgresql.org/docs/12/sql-createschema.html

In your original example the 'public' in public.amarkets.
So just to be complete \dn in psql will show you the schemas in a 
database. Given the search_path("$user",public) shown below I suspect 
you have only a public schema. $user matches a schema named for the 
current user and generally is not there.


The times returned below match, so I am at a loss for an explanation at 
the moment.

> 
> 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          +
>             |          |         |             |             | 
>
>> 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.f.lundin@gmail.com <mailto:b.f.lundin@gmail.com>
> 
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com