Re: Order by and timestamp

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

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(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          +
>            |          |         |             |             |
>
>> 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 <mailto:b(dot)f(dot)lundin(at)gmail(dot)com>
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com