To show the time and date we use the make_date() and make_time() functions. The make_date() function returns the date according to the input values and the make_time() function returns the time according to the parameters passed into the function. Both functions give errors when the input parameters have some issue, maybe due to the wrong data type or due to out-of-range parameters.
This article will show how make_data() and make_time() functions work. Let’s get started.
What Do make_date() and make_time() Functions do in PostgreSQL?
The things these functions have in common are that they take 3 parameters as input and return a type. The return type of value in the case of the make_date() function is the date and in the case of the make_time() function is time.
Let's discuss the make_date() function first.
The make_date() Function in PostgreSQL
The make_date() function manipulates the parameter it takes and converts it into date. These parameters are 3 in number: year, month, and day. remember that the sequence of these parameters has to be the exactly same otherwise, this could cause an error. The basic syntax for this function is:
make_date(Year INT, Month INT, Day INT);
All these parameters are required and the type of all of them should be “Integer”.
Now let's see an example to get more clarity over the concept.
Example
Consider the following query as an example:
SELECT make_date(2023, 8, 24);
The function in the above query will convert the parameters into a proper date format as follows:
In the above output, you can see that the returned type of the function is date. We can verify it by using the “pg_typeof()” function. The “pg_typeof()” function takes in a parameter and returns the data type of that argument.
The query will look like this:
SELECT pg_typeof(make_date(2023, 8, 24));
The output of the above query will clearly show the returned type:
So the make_date() function always returns the value having data type “date”.
Consider another example in which we will intentionally replace the value of a day parameter greater than 31 let's say it is 64. Let's see how the query responds to this case.
SELECT make_date(2023, 8, 64);
When we execute this query, the output will be:
The query gives an “out of range” error. This is because the value of parameter days is more than 31 as the day in a month can not be greater than 31. The same case will happen if the value of the month parameter is greater than 12.
Special case
As we have discussed above, the data type of each parameter should be INT. According to PostgreSQL documentation, it should be INT but there is a special case. If we pass the arguments in strings this also seems to work without any issue as soon as the values are in their proper range. This may be because they can be implicitly converted into integers. Let’s consider an example for this special case.
SELECT make_date('2023', '8', '24');By executing the above query we will get:
This means that under this condition the query also works properly.
But you have to make sure about one thing, the arguments must be valid to be converted into the INT. For example, if we execute the following query:
SELECT make_date('2023', 'August', '24');It will give an error that:
This is because the “August” is not valid and can not be converted into an integer lately.
If we want to consider the BC date we will have to place a minus(-) sign with the year such as:
SELECT make_date(-2023, 8, 2);
The output will give the BC date:
That was all about the make_date() function. Now we will move towards the make_time() function.
The make_time() Function in PostgreSQL
The make_time() function also works almost the same as make_date(). It manipulates the parameter it takes and converts it into time. These parameters are 3 in number: hour, min, and sec. Remember that the sequence of these parameters has to be exactly the same otherwise, this could cause an error. The basic syntax for this function is:
make_time(Hour INT, Minute INT, Second DOUBLE PRECISION);
All these parameters are required and the type of hour and min should be “Integer” while for a sec it has to be a “DOUBLE PRECISION”. The return value of the query is time or time without the time zone.
Now let's move towards an example.
Example
Consider the following query as an example:
SELECT make_time(10, 25, 27);
The function in the above query will convert the parameter into a proper time format as follows:
In the above output, you can see that the returned type of the function is time without time zone. We can verify it by using the “pg_typeof()” function.
SELECT pg_typeof(make_time(10, 25, 27));
The output of the above query will clearly show the returned type:
Consider another example in which we will intentionally replace the value of a min parameter greater than 60 let's say it is 87. Let's see how the query responds to this case.
SELECT make_time(10, 87, 27);
When we execute this query, the output will be:
The query gives an “out of range” error. This is because the value of parameter min is more than 60 as the minutes in an hour can not be greater than 60. The same case will happen if the value of the hours parameter is greater than 24 and seconds greater than 60.
Special case
Similar to the make_date() function, this function also has a special case. As we have discussed above, the data type of hour and minute parameter should be INT and for the second it is DOUBLE PRECISION. According to PostgreSQL documentation, they should be INT and DOUBLE PRECISION but there is a special case. If we pass the arguments in strings this also seems to work without any issue as soon as the values are in their proper range. Let’s consider an example for this special case.
SELECT make_time('10', '39', '35.05');By executing the above query we will get:
This means that under this condition the query also works properly. But if any of the arguments exceeds its range, it will give the out-of-range error.
Conclusion
There are many functions in PostgreSQL used to handle/manipulate the date and time. The functions make_date() and make_time() are significant among those. The make_date () function takes arguments, then creates and returns a date with them. The make_time() function takes arguments, then creates and returns a time without a time zone. The arguments have to be in range and specified data types.
