Functions for creating date-time values

Notice that there is no built-in function to create a timetz value—but this is of no consequence because of the recommendation, stated on the Date and time data types section's main page, to avoid using this data type.

function make_date() returns date

The make_date() built-in function creates a date value from int values for the year, the month-number, and the day-number. Here is the interesting part of the output from \df make_date():

 Result data type |           Argument data types
------------------+------------------------------------------
 date             | year integer, month integer, day integer

Here is an example:

with c as (
  select make_date(year=>2019, month=>4, day=>22) as d)
select pg_typeof(d)::text as "type", d::text from c;

This is the result:

 type |     d
------+------------
 date | 2019-04-22

Use a negative value for year to produce a BC result:

with c as (
  select make_date(year=>-10, month=>1, day=>31) as d)
select pg_typeof(d)::text as "type", d::text from c;

This is the result:

 type |       d
------+---------------
 date | 0010-01-31 BC

If you specify a non-existent date (like the year zero or, say, 30-February) then you get this error:

22008: date field value out of range

Try this:

drop procedure if exists confirm_expected_22008(int, int, int) cascade;

create procedure confirm_expected_22008(yy in int, mm in int, dd in int)
  language plpgsql
as $body$
declare
  d date;
begin
  d := make_date(year=>yy, month=>mm, day=>dd);
  assert false, 'Unexpected';

-- 22008: date field value out of range...
exception when datetime_field_overflow then
  null;
end;
$body$;

do $body$
begin
  call confirm_expected_22008( 0,  1, 20);
  call confirm_expected_22008(10, 13, 20);
  call confirm_expected_22008(10,  2, 30);
end;
$body$;

The final block finishes silently, demonstrating that the outcomes are as expected.

function make_time() returns (plain) time

The make_time() built-in function creates a plain time value from int values for the hour and the minutes-past-the-hour, and a real number for the seconds-past-the-minute. Here is the interesting part of the output from \df make_time():

    Result data type    |               Argument data types
------------------------+-------------------------------------------------
 time without time zone | hour integer, min integer, sec double precision

Here is an example:

with c as (
  select make_time(hour=>13, min=>25, sec=>20.123456) as t)
select pg_typeof(t)::text as "type", t::text from c;

This is the result:

          type          |        t
------------------------+-----------------
 time without time zone | 13:25:20.123456

If you specify a non-existent time (like, say, 25:00:00) then you get this error:

22008: time field value out of range

Try this:

drop procedure if exists confirm_expected_22008(int, int, double precision) cascade;

create procedure confirm_expected_22008(hh in int, mi in int, ss in double precision)
  language plpgsql
as $body$
declare
  t time;
begin
  t := make_time(hour=>hh, min=>mi, sec=>ss);
  assert false, 'Unexpected';

-- 22008: date field value out of range...
exception when datetime_field_overflow then
  null;
end;
$body$;

do $body$
begin
  call confirm_expected_22008(25, 30, 30.0);
  call confirm_expected_22008(13, 61, 30.0);
  call confirm_expected_22008(13, 30, 61.0);
end;
$body$;

The final block finishes silently, demonstrating that the outcomes are as expected.

function make_timestamp() returns (plain) timestamp

The make_timestamp() built-in function creates a plain timestamp value from int values for the year, the month-number, the day-number, the hour, and the minutes-past-the-hour, and a real number for the seconds-past-the-minute. Here is the interesting part of the output from \df make_timestamp():

      Result data type       |                                    Argument data types
-----------------------------+--------------------------------------------------------------------------------------------
 timestamp without time zone | year integer, month integer, mday integer, hour integer, min integer, sec double precision

Here is an example:

with c as (
  select make_timestamp(year=>2019, month=>4, mday=>22, hour=>13, min=>25, sec=>20.123456) as ts)
select pg_typeof(ts)::text as "type", ts::text from c;

This is the result:

            type             |             ts
-----------------------------+----------------------------
 timestamp without time zone | 2019-04-22 13:25:20.123456

Of course, there's a possibility here to cause the 22008 error. (The messages are spelled date field value out of range or time field value out of range according to what values were specified.) Try this:

drop procedure if exists confirm_expected_22008(int, int, int, int, int, double precision) cascade;

create procedure confirm_expected_22008(yy in int, mm in int, dd in int, hh in int, mi in int, ss in double precision)
  language plpgsql
as $body$
declare
  t timestamp;
begin
  t := make_timestamp(year=>yy, month=>mm, mday=>dd, hour=>hh, min=>mi, sec=>ss);
  assert false, 'Unexpected';

-- 22008: date field value out of range... OR time field value out of range...
exception when datetime_field_overflow then
  null;
end;
$body$;

do $body$
begin
  call confirm_expected_22008(   0,  6, 10, 13, 30, 30.0);
  call confirm_expected_22008(2019, 13, 10, 13, 30, 30.0);
  call confirm_expected_22008(2019,  6, 31, 13, 30, 30.0);
  call confirm_expected_22008(2019,  6, 29, 25, 30, 30.0);
  call confirm_expected_22008(2019,  6, 29, 13, 61, 30.0);
  call confirm_expected_22008(2019,  6, 29, 13, 30, 61.0);
end;
$body$;

The final block finishes silently, demonstrating that the outcomes are as expected.

function make_timestamptz() returns timestamptz

The make_timestamptz() built-in function creates a timestamptz value from int values for the year, the month-number, the day-number, the hour, and the minutes-past-the-hour, and a real number for the seconds-past-the-minute. Here is the interesting part of the output from \df make_timestamptz():

     Result data type     |                                            Argument data types
--------------------------+----------------------------------------------------------------------------------------------------------
 timestamp with time zone | year integer, month integer, mday integer, hour integer, min integer, sec double precision
 timestamp with time zone | year integer, month integer, mday integer, hour integer, min integer, sec double precision, timezone text

You can think of it like this: the function's name, make_timestamp() or make_timestamptz(), determines the return data type. The latter has an optional text parameter that, when specified, determines the timezone. If it is omitted, then the session's current timezone setting determines this fact.

Here is an example:

set timezone = 'UTC';
with c as (
  select make_timestamptz(year=>2019, month=>6, mday=>22, hour=>13, min=>25, sec=>20.123456, timezone=>'Europe/Helsinki') as tstz)
select pg_typeof(tstz)::text as "type", tstz::text from c;

This is the result:

           type           |             tstz
--------------------------+-------------------------------
 timestamp with time zone | 2019-06-22 10:25:20.123456+00

Helsinki is three hours ahead of UTC during the summer, so the specified one o'clock in the afternoon, Helsinki time, is shown as ten o'clock in the morning UTC.

Of course, there's a possibility here to cause the 22008 error. (The messages are spelled date field value out of range or time field value out of range according to what values were specified.) There's also a possibility to cause this error:

22023: time zone ... not recognized

Try this:

drop procedure if exists confirm_expected_22008_or_22023(int, int, int, int, int, double precision, text) cascade;

create procedure confirm_expected_22008_or_22023(yy in int, mm in int, dd in int, hh in int, mi in int, ss in double precision, tz in text)
  language plpgsql
as $body$
declare
  t timestamp;
begin
  t := make_timestamptz(year=>yy, month=>mm, mday=>dd, hour=>hh, min=>mi, sec=>ss, timezone=>tz);
  assert false, 'Unexpected';

-- 22008: date field value out of range... OR time field value out of range... OR time zone ... not recognized
exception when datetime_field_overflow or invalid_parameter_value then
  null;
end;
$body$;

do $body$
begin
  call confirm_expected_22008_or_22023(   0,  6, 10, 13, 30, 30.0, 'Europe/Helsinki');
  call confirm_expected_22008_or_22023(2019, 13, 10, 13, 30, 30.0, 'Europe/Helsinki');
  call confirm_expected_22008_or_22023(2019,  6, 31, 13, 30, 30.0, 'Europe/Helsinki');
  call confirm_expected_22008_or_22023(2019,  6, 29, 25, 30, 30.0, 'Europe/Helsinki');
  call confirm_expected_22008_or_22023(2019,  6, 29, 13, 61, 30.0, 'Europe/Helsinki');
  call confirm_expected_22008_or_22023(2019,  6, 29, 13, 30, 61.0, 'Europe/Helsinki');
  call confirm_expected_22008_or_22023(2019,  6, 29, 13, 30, 61.0, 'Europe/Helsinki');
  call confirm_expected_22008_or_22023(2019,  6, 29, 13, 30, 30.0, 'Europe/Lillehammer');
end;
$body$;

The final block finishes silently, demonstrating that the outcomes are as expected.

Workaround for creating BC plain timestamp and timestamptz values

You cannot use a negative actual argument for the year formal parameter with make_timestamp() and make_timestamptz(). This limitation persists through PostgreSQL Version 13. This seems to be a simple oversight—and PostgreSQL Version 14 removes the restriction. YSQL will suffer from this restriction until a version that's built using the PostgreSQL Version 14 SQL processing layer is released. Meanwhile, you can use this workaround:

drop function if exists my_make_timestamp(int, int, int, int, int, double precision) cascade;

create function my_make_timestamp(
  year int, month int, mday int, hour int, min int, sec double precision)
  returns timestamp
  language plpgsql
as $body$
declare
  bc  constant boolean   not null := year < 0 ;
  t   constant timestamp not null := make_timestamp(abs(year), month, mday, hour, min, sec);
begin
  return case bc
           when true then (t::text||' BC')::timestamp
           else           t
         end;
end;
$body$;

Test it like this:

select my_make_timestamp (year=>-10, month=>3, mday=>17, hour=>13, min=>42, sec=>19);

This is the result:

 0010-03-17 13:42:19 BC

You can implement the workaround my_make_timestamptz() using the same approach as for the function my_make_timestamp().

function to_timestamp() returns timestamptz

The to_timestamp() built-in function has two overloads. Each returns a timestamptz value. Here is the interesting part of the output from \_df to_timestamp():

     Result data type     | Argument data types
--------------------------+---------------------
 timestamp with time zone | double precision
 timestamp with time zone | text, text

The double precision overload interprets the input argument as the Unix epoch (i.e. the number of seconds since '1970-01-01 00:00:00+00'::timestamptz). See the section Date and time formatting functions for the (text, text) overload.

Here is an example:

set timezone = 'UTC';
with c as (
  -- 100 days after the start of the Unix epoch.
  select to_timestamp((60*60*24*1000)::double precision) as t)
select pg_typeof(t)::text as "type", t::text from c;

This is the result:

           type           |           t
--------------------------+------------------------
 timestamp with time zone | 1972-09-27 00:00:00+00

function make_interval() returns interval

The make_interval() built-in function creates an interval value from integral values for the number of years, months, weeks, days, hours, and minutes, and a real number for the number of seconds. Here is the interesting part of the output from \df make_interval(). (Each of the formal parameters has a default of zero. But this part of the \df output is elided here to improve readability.)

 Result data type |                                              Argument data types
------------------+----------------------------------------------------------------------------------------------------------------
 interval         | years integer, months integer, weeks integer, days integer, hours integer, mins integer, secs double precision

Here is an example:

with c as (
  select make_interval(secs=>250000.123456) as i)
select pg_typeof(i)::text as "type", i::text from c;

This is the result:

   type   |        i
----------+-----------------
 interval | 69:26:40.123456

You can use a negative actual argument for all of the formal parameters:

with c as (
  select make_interval(
    years=>-1,
    months=>-1,
    weeks=>-1,
    days=>-1,
    hours=>-1,
    mins=>-1,
    secs=>-1.1) as i)
select
  extract(years   from i) as years,
  extract(months  from i) as months,
  extract(days    from i) as days,
  extract(hours   from i) as hours,
  extract(mins    from i) as mins,
  extract(seconds from i) as secs
from c;

This is the result:

 years | months | days | hours | mins | secs
-------+--------+------+-------+------+------
    -1 |     -1 |   -8 |    -1 |   -1 | -1.1

Notice that while make_interval() has a formal parameter called weeks, you cannot use the name weeks to denote a field for extract(... from interval_value). (The attempt causes the 22023 error.)