Postgres has the age() function that returns the age in years, months, and days based on two dates.
This works fine unless you only want to return the age in years.
For example, you simply want to return a person’s age based on their birthday. You want something like 32 instead of 32 years 4 mons 67 days, which is what age() is likely to return.
Fortunately there’s an easy way to do this in PostgreSQL.
Continue readingIn PostgreSQL, you can use the AT TIME ZONE clause to convert a timestamp to another timezone.
In PostgreSQL, the make_interval() function creates an interval from years, months, weeks, days, hours, minutes and seconds fields.
You provide the years, months, weeks, days, hours, minutes and/or seconds fields, and it will return an interval in the interval data type.
Continue readingIn 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.
In PostgreSQL, the make_date() function allows you to create a date from its separate year, month and day fields.
Each date part is provided as an integer, and the result is returned as a date.
Continue readingIn PostgreSQL, the make_time() function allows you to create a time from its hour, minute and seconds fields.
In PostgreSQL, the make_timestamp() function allows you to create a timestamp from its year, month, day, hour, minute and seconds fields.
In PostgreSQL, the statement_timestamp() function returns the current date and time (including the time zone offset), at the start of the current statement.
The function doesn’t accept any parameters, so you can’t specify its precision.
Also, statement_timestamp() is a non-SQL-standard function.
In PostgreSQL, the transaction_timestamp() function returns the current date and time (including the time zone offset), at the start of the current transaction.
It’s the equivalent of the traditional Postgres function now().
It’s also similar to the current_timestamp function (when called without an argument), except that it’s named to clearly reflect what it does.
The transaction_timestamp() function doesn’t accept any parameters, so you can’t specify its precision, whereas current_timestamp can be called with or without a precision parameter.
Also, transaction_timestamp() is a non-SQL-standard function.
In PostgreSQL, the now() function returns the current date and time (including the time zone offset), at the start of the current transaction.
It’s the equivalent of the transaction_timestamp() function.
It’s also similar to the current_timestamp function (when called without an argument).
The now() function doesn’t accept any parameters, so you can’t specify its precision, whereas current_timestamp can be called with or without a precision parameter.
Also, now() is non-SQL-standard (as is the transaction_timestamp() function).