Database.Guide – Page 322

How to_timestamp() Works in PostgreSQL

September 5, 2021July 10, 2020 by Ian

According to the PostgreSQL documentation, there are two functions called to_timestamp():

One converts the Unix epoch to a timestamp. The Unix epoch is the number of seconds since 1970-01-01 00:00:00+00.
The other converts a string to a timestamp.

More specifically, each function converts the value to a timestamp with time zone value.

Although the Postgres documentation presents them as two separate to_timestamp() functions, I present them as if they’re one function that accepts either one argument, or two.

How to_date() Works in PostgreSQL

September 5, 2021July 10, 2020 by Ian

In PostgreSQL, you can use the to_date() function to convert a string to a date.

How current_timestamp() Works in PostgreSQL

January 28, 2021July 9, 2020 by Ian

In PostgreSQL, the current_timestamp() function returns the current date and time, including the time zone offset.

The timestamp returned is based on the start time of the current transaction.

How LocalTimeStamp() Works in PostgreSQL

January 28, 2021July 9, 2020 by Ian

In PostgreSQL, the localtimestamp() function returns the local date and time, without the time zone offset.

If you need the time zone offset, use current_timestamp() instead.

How date_part() Works in PostgreSQL

January 28, 2021July 8, 2020 by Ian

In PostgreSQL, the date_part() function retrieves subfields such as the year, month, hour, or minute, part from a date/time value.

It’s equivalent to the extract() function, although with a slightly different syntax.

How Extract() Works in PostgreSQL

October 18, 2021July 8, 2020 by Ian

In PostgreSQL, the extract() function retrieves subfields such as the year, month, hour, or minute, part from a date/time value.

It’s equivalent to the date_part() function, although with a slightly different syntax.

How justify_interval() Works in PostgreSQL

January 28, 2021July 8, 2020 by Ian

In PostgreSQL, the justify_interval() function adjusts an interval using justify_days and justify_hours. It allows you to use additional sign adjustments to adjust the interval.

How justify_days() Works in PostgreSQL

January 28, 2021July 8, 2020 by Ian

In PostgreSQL, the justify_days() function allows you to adjust an interval value, so that 30-day time periods are represented as months.

For example, if you have an interval of say, 30 days, justify_days() will return it as 1 mon.

How justify_hours() Works in PostgreSQL

January 28, 2021July 8, 2020 by Ian

In PostgreSQL, the justify_hours() function allows you to adjust an interval value, so that 24-hour time periods are represented as days.

For example, if you have an interval of say, 24 hours justify_hours() will return it as 1 day.

Return a List of Timezones Supported by PostgreSQL

July 7, 2020 by Ian

When working with PostgreSQL, you may occasionally find yourself in the situation where you need a list of timezones that Postgres recognises.

For example, you could be trying to set the timezone for your current session, or you could be using one of the datetime functions that allow you to manipulate the timezone.

Below are two views and two functions that return a list of timezones.