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:

This is the result:

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

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

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:

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:

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:

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:

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:

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:

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:

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:

Test it like this:

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:

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:

This is the result:

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

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

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.)