In PostgreSQL, the make_timestamp() function allows you to create a timestamp from its year, month, day, hour, minute and seconds fields.

Syntax

The function has the following syntax:

make_timestamp(year int, month int, day int, hour int, min int, sec double precision)

Where year, month, and day are integers representing the year, month, and day of the date, and hour is the hour part, min is the minutes part, and sec is the seconds part.

The hour and the minutes are provided as an integer, the seconds are provided as double precision.

The result is returned as a timestamp. More precisely, the it’s returned as time without time zone.

Example

Here’s a basic example to demonstrate.

SELECT make_timestamp(2020, 10, 25, 9, 30, 17.12);

Result:

2020-10-25 09:30:17.12

And we can verify the return type with the following query.

SELECT pg_typeof(make_timestamp(2020, 10, 25, 9, 30, 17.12));

Result:

timestamp without time zone

The pg_typeof() function returns the data type of its argument, and so I passed make_timestamp() as the argument..

Out of Range

If any of the arguments are out of the range of the possible values for its date part, you’ll get an “out of range” error.

SELECT make_timestamp(2020, 13, 25, 9, 30, 17.12);

Result:

ERROR: date field value out of range: 2020-13-25

In this case the month was out of range, and so the message explained that the date field value was out of range.

If only the time field value is out of range, the error is worded accordingly.

SELECT make_timestamp(2020, 10, 25, 25, 30, 17.12);

Result:

ERROR: time field value out of range: 25:30:17.12

Passing Strings as Arguments

The Postgres documentation states that the arguments must be integers (and double precision in the case of the seconds argument), but passing strings also works (probably because they’re implicitly converted to integers), as long as each argument is within its proper range.

SELECT make_timestamp('2020', '12', '25', '10', '30', '17.12');

Result:

2020-12-25 10:30:17.12

Again we can use pg_type() to check the resulting data type.

SELECT pg_typeof(make_timestamp('2020', '12', '25', '10', '30', '17.12'));

Result:

timestamp without time zone

However, you still need to make sure each argument would be valid once converted to an integer, otherwise you’ll get an error.

SELECT pg_typeof(make_timestamp('2020', '13', '25', '10', '30', '17.12'));

Result:

ERROR: date field value out of range: 2020-13-25

Timestamp With Timezone

To create a timestamp with time zone value, use the make_timestamptz() function.