PostgreSQL – Timezone for ‘Timestamp Without Timezone’ in psql

postgresqlpsqltimestamp

What I mean is the following: if I create a timestamp without timezone (timestamp) column and store there some value, internally microseconds are stored, but when issuing select this is converted to a string with date and time. But in which timezone the converted time is?
I tried to change timezone in my computer (PostgreSQL is running locally, so I reopened psql), tried changing it in postgresql.conf (restarting the service) and setting timezone per session. However as far as I saw, the output doesn't change. I'm sure I'm missing something. Thanks!

Sample code:

create table tzo (wotz timestamp);
insert into tzo values (now());
table tzo;

Output:

            wotz            
----------------------------
 2020-08-11 17:51:37.244901
(1 row)

Best Answer

timestamp without time zone is time zone agnostic, so it is not adjusted to any time zone on display. This data type just doesn't have a time zone.

You can convert it to a certain time zone with AT TIME ZONE:

SHOW timezone;

   TimeZone    
---------------
 Europe/Vienna
(1 row)

SELECT TIMESTAMP WITHOUT TIME ZONE '2020-08-01 12:00:00' AT TIME ZONE 'UTC';

        timezone        
------------------------
 2020-08-01 14:00:00+02
(1 row)

Then the timestamp is interpreted as Viennese time, converted to UTC and displayed in my local Viennese time zone, which is offset two hours.

Related Solutions

MySQL timestamp timezone handling

TIMESTAMP stores the number of seconds from 1970-01-01 00:00:01 to now. It automatically converts to the date and time format when you retrieve the data.

CURRENT_TIMESTAMP(): Returns the current date time with your timezone configured. UTC_TIMESTAMP(): Returns the current date and time using UTC timezone.

If you're using a timezone that's not UTC, these functions always return different values, equal otherwise.

MySQL always stores the date and time in UTC so it can be easily converted to different timezones. NOW() or CURRENT_TIMESTAMP() return the date/time plus your timezone configured.

UTC_TIMESTAMP() just returns the date/time ignoring your timezone setting.

PostgreSQL – How to Display Only Table Names with psql

In PostgreSQL (as described in documentation, The Information Schema):

SELECT table_name
FROM information_schema.tables
WHERE table_type='BASE TABLE'
AND table_schema='public';

For MySQL you would need table_schema='dbName' and for MSSQL remove that condition.

Notice that "only those tables and views are shown that the current user has access to". Also, if you have access to many databases and want to limit the result to a certain database, you can achieve that by adding condition AND table_catalog='yourDatabase' (in PostgreSQL).

If you'd also like to get rid of the header showing row names and footer showing row count, you could either start the psql with command line option -t (short for --tuples-only) or you can toggle the setting in psql's command line by \t (short for \pset tuples_only). This could be useful for example when piping output to another command with \g [ |command ].

Best Answer

Related Solutions

MySQL timestamp timezone handling

PostgreSQL – How to Display Only Table Names with psql

Related Question