Date formatting in PostgreSQL

select action_type, to_char(action_date,'DD Mon YYYY'), count(*) from actions group by action_type,

action_type     |   to_char   | count
-----------------+-------------+-------
 action1 | 27 Oct 2020 | 47831
 action1 | 22 Oct 2020 |   640
 action1 | 20 Oct 2020 |     1
 action1 | 22 Oct 2020 |  1654
 action1 | 26 Oct 2020 |   290
 action2 | 21 Oct 2020 |     8
 action2 | 27 Oct 2020 |   140
 action2 | 26 Oct 2020 |   900

Documentation here

How to use the TIMESTAMP_TO_SCN function

select timestamp_to_scn(to_timestamp('2020-05-11 14:36:22', 'YYYY-MM-DD HH24:MI:SS')) "scn" from dual;

scn
----------
20325895

Reversed:

select scn_to_timestamp(20325895) "timestamp" from dual;

timestamp
----------------------------------
11-MAY-20 02.36.20.000000000 PM

Some simple timestamp expressions

Here is a simple template for how to add hours, minutes and seconds using Oracle timestamp expressions:

select sysdate, sysdate+1/24 "+ 1hr",  sysdate+1/24/60 "+ 1 min",  sysdate+1/24/60/60 "+ 1 sec", sysdate+10/24/60/60 "+ 10 sec"
from dual;

SYSDATE	+ 1hr	+ 1 min	+ 1 sec	+ 10 sec
17.01.2019 13:34:38	17.01.2019 14:34:38	17.01.2019 13:35:38	17.01.2019 13:34:39	17.01.2019 13:34:48

How the DBTIMEZONE is set in an Oracle database

A customer wanted me to look into why his two databases running the exact same application code were returning different time zones.

select name, created,(select dbtimezone from dual) "dbtimezone"
from v$database;

NAME	CREATED	dbtimezone
testdb1	25.04.2016 16:40:12	+02:00

NAME	CREATED	dbtimezone
testdb2	08.11.2017 14:07:51	+01:00

The os-command date +"%Z %z" returned CEST +0200 on both servers. Why the difference?

It didn't take long to find a good answer from Oracle about this topic.


Oracle states:

The database timezone is the time zone of the location where the database is installed

and

Use the SET TIME_ZONE clause to set the time zone of the database. If you do not specify the SET TIME_ZONE clause, then the database uses the operating system time zone of the server.

And also, from the document "Timestamps & time zones - Frequently Asked Questions (Doc ID 340512.1)":

If not specified with the CREATE DATABASE statement, the database time zone defaults to the server's O/S time zone offset. Note that if your server runs in a time zone affected by DST, then the database time zone would default to whatever the current offset is at the time of database creation, so you can have databases created with the same script on the same server with a different database time zone.

In Norway, we use DST, more commonly referred to as "summertime" and "wintertime". The first datbase was installed April 25th, after the server had switched to summertime. The second database was installed November 8th, after the server had switched to wintertime.

Oracle continues with the following about the database time zone:


The database time zone is not as important as it sounds. First of all it does not influence functions like sysdate, or systimestamp.

These function take their contents (date and time, and in the case of systimestamp also time zone) completely from the OS without any "Oracle" intervention.

and

A common misconception is that the database timezone needs to be "your" timezone. This is NOT true.
The database timezone has NO relation with "where" the server is located.
There is NO advantage whatsoever in using your timezone or a named timezone as database timezone.
The best setting for dbtimezone is simply +00:00 (or any other OFFSET like -09:00, +08:00, ...),

if your current dbtimezone value is an OFFSET then please leave it like it is.

How to display a SYSTIMESTAMP data type as a TIMESTAMP WITH TZ

To display a value of datatype SYSTIMESTAMP as TIMESTAMP WITH TIME ZONE, use TO_CHAR first, then convert to TIMESTAMP WITH TIMEZONE.

The last expression adds one hour, which can be done after the conversion between the datatypes is complete:

select  TO_CHAR(SYSTIMESTAMP,'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm'),
        TO_TIMESTAMP_TZ(to_char(SYSTIMESTAMP,'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm'),'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm'),
        TO_TIMESTAMP_TZ(to_char(SYSTIMESTAMP,'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm'),'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')+1/24
from dual;

systimestamp	TIMESTAMP_TZ	TIMESTAMP_TZ + 1hr
2017/02/01 10:36:28.158921 +01:00	01/02/2017 10:36:28,158921000 +01:00	01.02.2017 11:36:28