postgresql date types - ghdrako/doc_snipets GitHub Wiki

Current

  • CURRENT_TIMESTAMP,transaction_timestamp(),now() - functions all return values based on the start time of the current transaction
  • statement_timestamp() returns the start time of the current statement (more specifically, the time of receipt of the latest command message from the client)
  • CURRENT_DATE eqivalent of CURRENT_TIMESTAMP::DATE
  • clock_timestamp() returns the actual current time, and therefore its value changes even within a single SQL command

Note: statement_timestamp() is STABLE just like all variants of now(). (They always return the same value within the same SQL command). But clock_timestamp() necessarily is only VOLATILE. The difference may be significant.

Convert timestamp to date prevent using index

Wrong good
scheduled_departure ::date BETWEEN '2023-08-17' AND '2023-08-18' scheduled_departure >='2023-08-17' AND scheduled_departure <'2023-08-19'
update_ts::date=CURRENT_DATE update_ts>= CURRENT_DATE AND update_ts< CURRENT_DATE +1
coalesce(actual_departure, scheduled_departure) BETWEEN '2023-08-17' AND '2023-08-18' (actual_departure BETWEEN '2023-08-17' AND '2023-08-18') OR (actual_departure IS NULL AND scheduled_departure BETWEEN '2023-08-17' AND '2023-08-18')

Decorated literal

we decorate the literal with its data type so that PostgreSQL doesn’t have to guess what it is.

select date '2010-02-20';
select timestamp '2024-10-01 00:00:00';

Overlaps operator

(start1, end1) OVERLAPS (start2, end2)
(start1, length1) OVERLAPS (start2, length2)

This expression yields true when two time periods (defined by their endpoints) overlap, false when they do not overlap. The endpoints can be specified as pairs of dates, times, or time stamps; or as a date, time, or time stamp followed by an interval. When a pair of values is provided, either the start or the end can be written first; OVERLAPS automatically takes the earlier value of the pair as the start. Each time period is considered to represent the half-open interval start <= time < end, unless start and end are equal in which case it represents that single time instant. This means for instance that two time periods with only an endpoint in common do not overlap.

SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');
Result: true
SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');
Result: false
SELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS
       (DATE '2001-10-30', DATE '2001-10-31');
Result: false
SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS
       (DATE '2001-10-30', DATE '2001-10-31');
Result: true

 new_meetings AS (
    SELECT
        id,
        starts_at::timestamptz as starts_at,
        ends_at::timestamptz as ends_at
    FROM (VALUES
        ('A', '2021-10-01 11:10 UTC', '2021-10-01 11:55 UTC'),
        ('B', '2021-10-01 11:20 UTC', '2021-10-01 12:05 UTC'),
        ('C', '2021-10-01 11:20 UTC', '2021-10-01 11:55 UTC'),
        ('D', '2021-10-01 11:10 UTC', '2021-10-01 12:05 UTC'),
        ('E', '2021-10-01 11:15 UTC', '2021-10-01 12:00 UTC'),
        ('F', '2021-10-01 12:00 UTC', '2021-10-01 12:10 UTC'),
        ('G', '2021-10-01 11:00 UTC', '2021-10-01 11:15 UTC')
    ) as t(
        id,   starts_at,               ends_at
    )
)
SELECT
    *
FROM
    meetings, new_meetings
WHERE
    (new_meetings.starts_at, new_meetings.ends_at)
        OVERLAPS (meetings.starts_at, meetings.ends_at);

       starts_at     β”‚        ends_at      β”‚ id β”‚       starts_at     β”‚        ends_at
─────────────────────┼─────────────────────┼────┼─────────────────────┼────────────────────
 2021-10-01 11:15:00 β”‚ 2021-10-01 12:00:00 β”‚ A  β”‚ 2021-10-01 11:10:00 β”‚ 2021-10-01 11:55:00
 2021-10-01 11:15:00 β”‚ 2021-10-01 12:00:00 β”‚ B  β”‚ 2021-10-01 11:20:00 β”‚ 2021-10-01 12:05:00
 2021-10-01 11:15:00 β”‚ 2021-10-01 12:00:00 β”‚ C  β”‚ 2021-10-01 11:20:00 β”‚ 2021-10-01 11:55:00
 2021-10-01 11:15:00 β”‚ 2021-10-01 12:00:00 β”‚ D  β”‚ 2021-10-01 11:10:00 β”‚ 2021-10-01 12:05:00
 2021-10-01 11:15:00 β”‚ 2021-10-01 12:00:00 β”‚ E  β”‚ 2021-10-01 11:15:00 β”‚ 2021-10-01 12:00:00

Convert timestamp to date

select DATE(CURRENT_TIMESTAMP);
select CURRENT_TIMESTAMP::date;

Extract from date

select date::date, 
       extract('isodow' from date) as dow, 
       to_char(date, 'dy') as day, 
       extract('isoyear' from date) as "iso year", 
       extract('week' from date) as week, 
       extract('day' from  
                 (date + interval '2 month - 1 day') 
              )  
       as feb, 
       extract('year' from date) as year, 
       extract('day' from 
                 (date + interval '2 month - 1 day') 
              ) = 29 
       as leap 
  from generate_series(date '2000-01-01', 
                       date '2010-01-01', 
                       interval '1 year') 
       as t(date);

date β”‚ dow β”‚ day β”‚ iso year β”‚ week β”‚ feb β”‚ year β”‚ leap 
2000-01-01 β”‚ 6 β”‚ sat β”‚ 1999 β”‚ 52 β”‚ 29 β”‚ 2000 β”‚ t 
2001-01-01 β”‚ 1 β”‚ mon β”‚ 2001 β”‚ 1 β”‚ 28 β”‚ 2001 β”‚ f 
2002-01-01 β”‚ 2 β”‚ tue β”‚ 2002 β”‚ 1 β”‚ 28 β”‚ 2002 β”‚ f 
2003-01-01 β”‚ 3 β”‚ wed β”‚ 2003 β”‚ 1 β”‚ 28 β”‚ 2003 β”‚ f 
2004-01-01 β”‚ 4 β”‚ thu β”‚ 2004 β”‚ 1 β”‚ 29 β”‚ 2004 β”‚ t 
2005-01-01 β”‚ 6 β”‚ sat β”‚ 2004 β”‚ 53 β”‚ 28 β”‚ 2005 β”‚ f 
2006-01-01 β”‚ 7 β”‚ sun β”‚ 2005 β”‚ 52 β”‚ 28 β”‚ 2006 β”‚ f 
2007-01-01 β”‚ 1 β”‚ mon β”‚ 2007 β”‚ 1 β”‚ 28 β”‚ 2007 β”‚ f 
2008-01-01 β”‚ 2 β”‚ tue β”‚ 2008 β”‚ 1 β”‚ 29 β”‚ 2008 β”‚ t 
2009-01-01 β”‚ 4 β”‚ thu β”‚ 2009 β”‚ 1 β”‚ 28 β”‚ 2009 β”‚ f 
2010-01-01 β”‚ 5 β”‚ fri β”‚ 2009 β”‚ 53 β”‚ 28 β”‚ 2010 β”‚ f 
 (11 rows) It

By definition, ISO weeks start on Mondays and the ??rst week of a year contains January 4 of that year. In other words, the first Thurs-day of a year is in week 1 of that year.

\set beginning '2017-04-01' 
 \set months 3 
Select name
 From race
 where date >= date :'beginning' 
   and date < date :'beginning' 
       + :months * interval '1 month';

Java jdbc exeute analyze

final Statement st2 = connection.createStatement();
st2.executeUpdate("VACUUM FULL ANALYZE VERBOSE");
st2.close();

show DateStyle;

select * from pg_settings where name ='DateStyle';
name | DateStyle
setting | ISO, MDY
[..]
sourcefile |
sourceline |
pending_restart | f

Using the pg_settings view, we can view the parameters set in the postgresql.conf configuration file. In the preceding result, we can see that the configuration for displaying the date is MDY (month/day/year). If we want to change this parameter globally, we have to edit the postgresql.conf file.

set DateStyle='ISO MDY' ;

select '12-31-2020'::date;
date
------------
2020-12-31

select to_date('31/12/2020','dd/mm/yyyy') ;
to_date
------------
2020-12-31

create table new_posts as select pk,title,created_on::timestamp
with time zone as created_on_t, created_on::timestamp without time zone as
create_on_nt from posts;

extract(y From current_date) as year

date_trunc('datepart', field)

datepart: millennium,century,decade,year,quarter,month,week,day,hour,minute,second,milliseconds,microseconds

DATE_TRUNC('month', now()) 
SELECT DATE_TRUNC('hour', TIMESTAMP '2017-03-17 02:09:30');
SELECT date_trunc('minute', TIMESTAMP '2017-03-17 02:09:30');
SELECT
    date_trunc('month', rental_date) m,
    COUNT (rental_id)
FROM
    rental
GROUP BY
    m
ORDER BY
    m;

SELECT
    date_trunc('month', order_date) AS month,
    COUNT(*) AS total_orders,
    SUM(total_amount) AS monthly_total
FROM
    orders
GROUP BY
    date_trunc('month', order_date)
ORDER BY
    month;

Convert to char

select to_date(20230219',YYYYMMDD');
select to_timestamp('28 Oct 2020 09:36:47 am','DD Mon YYYY HH:MI:SS AM');
select to_char(clock_change_date, 'Mon DD, YYYY') from clock_change;
SELECT TO_CHAR('2016-08-12 16:40:32'::TIMESTAMP, 'DD Mon YYYY HH:MI:SSPM');
SELECT TO_CHAR('2016-08-12 16:40:32'::TIMESTAMP,'"Today is "FMDay", the "DDth" day of the month of "FMMonth" of "YYYY');
SELECT TO_CHAR('2016-08-12 16:40:32'::TIMESTAMP, 'TMDay, DD" de "TMMonth" del aΓ±o "YYYY');  -- TM (translation mode) modifier
--This option uses the localization setting of the server running PostgreSQL or the client connecting to it.

last day of month

SELECT (DATE_TRUNC('MONTH', ('201608'||'01')::DATE) + INTERVAL '1 MONTH - 1 day')::DATE;

TIMESTAMP and TIMESTAMPTZ

PostgreSQL provides you with two temporal data types for handling timestamp:

  • timestamp: a timestamp without timezone one.
  • timestamptz: timestamp with a timezone.

PostgreSQL stores the timestamptz in UTC value.

  • When you insert a value into a timestamptz column, PostgreSQL converts the timestamptz value into a UTC value and stores the UTC value in the table.
  • When you query timestamptz from the database, PostgreSQL converts the UTC value back to the time value of the timezone set by the database server, the user, or the current database connection.
SELECT 
pg_column_size('2000-01-01 00:00:00 +00:00'::timestamp) as "timestamp byte size",
pg_column_size('2000-01-01 00:00:00 +00:00'::timestamptz) as "timestamptz byte size";
+--------------------+-----------------------+
|timestamp byte size | timestamptz byte size |
+--------------------+-----------------------+
|                  8 |                     8 |
+--------------------+-----------------------+
select pg_column_size(timestamp without time zone 'now'),
       pg_column_size(timestamp with time zone 'now');

PostgreSQL doesn’t store the time zone they come from with your timestamp.

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system’s TimeZone parameter, and is converted to UTC using the offset for the timezone zone.

CREATE TABLE timestamp_demo (
    ts TIMESTAMP, 
    tstz TIMESTAMPTZ
);

SET timezone = 'America/Los_Angeles';
SHOW TIMEZONE;
INSERT INTO timestamp_demo (ts, tstz)
VALUES('2016-06-22 19:10:25-07','2016-06-22 19:10:25-07');
SELECT 
   ts, tstz
FROM 
   timestamp_demo;
         ts          |          tstz
---------------------+------------------------
 2016-06-22 19:10:25 | 2016-06-22 19:10:25-07
(1 row)

SET timezone = 'America/New_York';
SELECT
	ts,
	tstz
FROM
	timestamp_demo;
Code language: SQL (Structured Query Language) (sql)

         ts          |          tstz
---------------------+------------------------
 2016-06-22 19:10:25 | 2016-06-22 22:10:25-04
(1 row)

SELECT NOW();

now() function always returns the same timestamp within a single transaction. If you want to see the clock running while in a transaction, use the clock_timestamp() function instead.

SELECT CURRENT_TIMESTAMP;
SELECT TIMEOFDAY();
 timeofday
-------------------------------------
 Wed Jun 22 20:51:12.632420 2016 PDT
SHOW TIMEZONE;
Code language: SQL (Structured Query Language) (sql)

      TimeZone
---------------------
 America/Los_Angeles
(1 row)

-- To convert 2016-06-01 00:00 to America/New_York timezone
SELECT timezone('America/New_York','2016-06-01 00:00');
Code language: SQL (Structured Query Language) (sql)

      timezone
---------------------
 2016-06-01 03:00:00


-- Note that we pass the timestamp as a string to the timezone() function, PostgreSQL casts it to timestamptz implicitly. It is better to  -- cast a timestamp value to the timestamptz data type explicitly as the following statement:

SELECT timezone('America/New_York','2016-06-01 00:00'::timestamptz);

SET TIME ZONE β€˜UTC+3’;

SELECT '2000-09-15 19:00+11:00'::TIMESTAMP, '2000-09-15 19:00+11:00'::TIMESTAMPTZ;
SELECT β€˜2019-07-14 17:00:00.545454’::TIMESTAMP(0); – rounded up


select timestamptz '2017-01-08 04:05:06',  -- handled by the timezone session parameter
       timestamptz '2017-01-08 04:05:06+02'; --  input the time zone in the timestamp values directly

---  convert the stored timestamp to the client’s timezone at the database layer
SELECT '2000-01-01 00:00:00 +00:00'::timestamptz at time zone 'Europe/Rome' as "Rome's timestamp";

CURRENT_TIMESTAMP(precision)

If you omit the precision argument, the CURRENT_TIMESTAMP() function will return a TIMESTAMP with a time zone that includes the full fractional seconds precision available. The CURRENT_TIMESTAMP() function returns a TIMESTAMP WITH TIME ZONE that represents the date and time at which the transaction started.Internally, the CURRENT_TIMESTAMP() is implemented with the NOW() function

CREATE TABLE note(
    note_id serial PRIMARY KEY,
    message varchar(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

TRANSACTION_TIMESTAMP() function is equivalent to the CURRENT_TIMESTAMP function.

TIME and TIMETZ

  • TIME is still encoded with 8 bytes, representing microseconds since midnight.
  • TIMETZ is encoded with 12 bytes, with 8 bytes representing microseconds since midnight and 4 bytes for storing the time zone offset in seconds west of UTC
 CURRENT_TIME

select β€˜10:00’::time + β€˜14 hours’::interval;
select β€˜17:00:00.545454’::time(0), β€˜17:00:00.545454+03’::timetz(0);  -- parsing

Time Intervals

An interval describes a duration, like a month or two weeks, or even a millisecond:

Interval can be specify in two wys

  • INERVAL keyward
SELECT SUM(total_amount) 
FROM orders
WHERE order_date >= NOW() - INTERVAL '90 days';

SELECT SUM(total_amount) 
FROM orders
WHERE order_date BETWEEN (NOW() - INTERVAL '90 days') AND (NOW() - INTERVAL '30 days')
  • as a cast
SELECT SUM(total_amount) 
FROM orders
WHERE order_date >= NOW() - '90 days'::interval;

set intervalstyle to postgres;

select interval '1 month',
       interval '2 weeks',
       2 * interval '1 week',
       78389 * interval '1 ms';
interval β”‚ interval β”‚ ?column? β”‚   ?column?   
══════════β•ͺ══════════β•ͺ══════════β•ͺ══════════════
 1 mon    β”‚ 14 days  β”‚ 14 days  β”‚ 00:01:18.389
(1 row)


set intervalstyle to postgres_verbose;

select interval '1 month',
       interval '2 weeks',
       2 * interval '1 week',
       78389 * interval '1 ms';
 interval β”‚ interval  β”‚ ?column?  β”‚      ?column?       
══════════β•ͺ═══════════β•ͺ═══════════β•ͺ═════════════════════
 @ 1 mon  β”‚ @ 14 days β”‚ @ 14 days β”‚ @ 1 min 18.389 secs
(1 row)

select d::date as month,
       (d + interval '1 month' - interval '1 day')::date as month_end,
       (d + interval '1 month')::date as next_month,
       (d + interval '1 month')::date - d::date as days

  from generate_series(
                       date '2017-01-01',
                       date '2017-12-01',
                       interval '1 month'
                      )
       as t(d);

When you attach an interval to a date or timestamp in PostgreSQL then the number of days in that interval adjusts to the specific calendar entry you’ve picked. Otherwise, an interval of a month is considered to be 30 days.

Interwal otrzymam jak odejmiemy dwa znaczniki czasowe od siebie

Select TIMESTAMP '2024-03-01 00:00:00' - TIMESTAMP '2024-02-01' as deys_in_febuaryn;

29 days

Odejmujac dwie daty otrzymamy liczbΔ™ dni

Select DATE '2024-03-01' - DATE '2024-02-01' as deys_in_febuaryn;

29