_edited_edited.png)
Most of us struggle with operating date and time in PostgreSQL. But you know what, PostgreSQL has many built in date functions which can assist us in performing various operations on date and time values easily and effectively. With the help of these in built functionalities we can get the current date and time, calculate intervals, extract various date and time components, format dates etc. In this blog, let’s look at the commonly used date and time functions along with some examples.
Current Date and time functions:
1. CURRENT_DATE :
The current date function returns today’s i.e. Current date as per the database session’s time zone. It does not show the time information.
Example :
2. CURRENT_TIME:
The current time function returns the current time and does not include any of the date information. This function gives the time with the time zone value. Current time accepts one optional argument named Precision. Precision gives the time value along with the fractional seconds accuracy, that means the full available precision. The output of current time with precision results up to 6 decimals.
Example:
3. CURRENT_TIMESTAMP:
The current timestamp function returns the current date and time along with the time zone. The following function also accepts precision as an optional argument.
Example:
4. LOCALTIME:
The local time function returns the current time of the machine on which the PostgreSQL is running. The output value does not consist of time zone. The result of this function returns the value in hh:mm:ss.nnnnnn where,
hh = is 2 digits hours
mm = is 2 digits minutes
ss = 2 digits seconds
nnnnnn = fractional seconds (can be 0–6 digits)
We have an option to display the time with the required precision by utilizing brackets with an integer from 0–6 as an argument. By executing this we will get the specified fractional seconds in the output.
Example:
5. LOCALTIMESTAMP:
The local timestamp function returns the current date and time of the machine on which the PostgreSQL is running. The output of this function does not contain the timestamp. Like local time this function can also accept one optional argument Precision. If Precision isn’t specified the returning value comprises of 6 digit fractional seconds.
Example:
6. STATEMENT_TIMESTAMP():
The statement timestamp function returns the date and time representing the point in time the current query started. This function outputs date and time along with the time zone. It does not accept any other arguments. Also note that we need to add parenthesis ‘()’ after the function for its execution.
Example:
7. CLOCK_TIMESTAMP():
The clock time stamp function returns the current date and time according to the changes during statement execution. Meaning, as the statement is executed the timestamp will also change correspondingly.
It does not accept any arguments and returns the date and time along with time zone. Whenever the clock timestamp function is called multiple times within a query, it will give varied results.
Examples:
In the above example the output shows the actual timestamp between the calls within the same query. Notice the difference in seconds in the 3 outputs as we have added pg sleep of 2 secs.
8. NOW():
The now function returns the date and time with time zone of the database server.
It does not accept any other arguments. This function returns the current timestamp depending upon the database servers time zone setting. If we change the default time zone to any other, the now function results in giving us the timestamp along with the time zone of the changed setting.
Examples:
Converting to Date and time functions:
1. TO_TIMESTAMP() :
The function to timestamp converts a string to timestamp according to the specified format. We can convert the string into different formats but one at a time using this function. To timestamp requires two arguments.
1) Timestamp : It is the text/string with date or time to convert into timestamp by using the format given as the second argument.
2) Format : It is the format in which we want to convert the given string in the first argument.
Example:
In the above example,
· YYYY: It is the four-digit year 2024
· MM: It is the month 05.
· DD: It is the day 15th.
· HH: It is hour 4.
· MI: It is minute 35.
· SS: It is the second 2
2. TO_DATE():
The to date function is used to convert string/text to a date value. To date accepts two arguments.
1) text : It is an input string that you want to convert to a date in the format provided as the second argument.
2) Format : It is the format in which we want to convert the given string in the first argument.
If you give an invalid date string, the To_Date function tries to convert it to a valid date and throws an error message if fails to convert.
Example:
3. MAKE_DATE():
The make date function allows us to create a date value from the specified year, month, and date values. It returns the value of a date type. This function automatically handles leap years.
Example:
4. MAKE_TIME():
The make time function allows us to convert a time value from hour, minute and second values. It returns a time value as result. If we provide invalid values of hours, mins, seconds for conversion, the function will throw error.
In this syntax of make time:
· hour: The hour part of the time. The valid range for the hour is from 0 to 23. 0 represents midnight (12:00 AM) and 24 represents noon.
· min: The minute part of the time. The valid range for the second is from 0 to 59.
· sec: The second within a minute. Its valid range is from 0 to 59.999999.
Example:
