In PostgreSQL, the make_timestamptz() function allows you to create a timestamp with time zone from its year, month, day, hour, minute and seconds fields, as well as an optional timezone field.

Syntax

The function has the following syntax:

make_timestamptz(year int, month int, day int, hour int, min int, sec double precision, [ timezone text ])

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 optional timezone argument is for the timezone. If it’s not specified, the current timezone is used.

The result is returned as a time with time zone.

Example

Here’s a basic example to demonstrate.

SELECT make_timestamptz(1999, 03, 14, 10, 35, 12.15);

Result:

1999-03-14 10:35:12.15+10

In this case I didn’t provide a timezone, so it used the current timezone.

We can verify the return type with the following query.

SELECT pg_typeof(make_timestamptz(1999, 03, 14, 10, 35, 12.15));

Result:

timestamp with time zone

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

Specify a Timezone

In this example I explicitly provide a timezone.

SELECT make_timestamptz(1999, 03, 14, 10, 35, 12.15, 'Indian/Mauritius');

Result:

1999-03-14 16:35:12.15+10

Here’s another example, but with various timezone values.

\x
SELECT 
  make_timestamptz(2000, 01, 01, 00, 01, 00.00, 'ACDT'),
  make_timestamptz(2000, 01, 01, 00, 01, 00.00, 'ACST'),
  make_timestamptz(2000, 01, 01, 00, 01, 00.00, 'ACT'),
  make_timestamptz(2000, 01, 01, 00, 01, 00.00, '+01'),
  make_timestamptz(2000, 01, 01, 00, 01, 00.00, '-01'),
  make_timestamptz(2000, 01, 01, 00, 01, 00.00, 'NZ');

Result (using vertical output):

make_timestamptz | 1999-12-31 23:31:00+10
make_timestamptz | 2000-01-01 00:31:00+10
make_timestamptz | 2000-01-01 15:01:00+10
make_timestamptz | 2000-01-01 09:01:00+10
make_timestamptz | 2000-01-01 11:01:00+10
make_timestamptz | 1999-12-31 21:01:00+10

In this example I used \x to enable expanded display (also known as “vertical output”) in order to make the results easier to read.

Entering \x toggles expanded display (i.e. it enables and disables it).

See Return a List of Timezones Supported by PostgreSQL to get a list of valid timezones.

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_timestamptz(1999, 03, 32, 10, 35, 12.15);

Result:

ERROR: date field value out of range: 1999-03-32

In this case the day was 32, which is 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_timestamptz(1999, 03, 30, 10, 61, 12.15);

Result:

ERROR: time field value out of range: 10:61:12.15

In this case the minutes argument is 61, which is out of range.

Timestamp Without Timezone

To create a timestamp without a timezone, use the make_timestamp() function.