In PostgreSQL, you can convert a given date to its Julian day representation by using the to_char() function.
To do this, pass the date as the first argument, and 'J' as the second.
Tag: how to
Return a List of Timezones Supported by PostgreSQL
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.
Continue readingGet the Century from a Date in PostgreSQL
In Postgres, you can use the to_char() function to return the century, based on a given date.
To do this, use CC as the second argument. This returns the two-digit century based on the date provided.
How to Create Ordinal Numbers in PostgreSQL
In PostgreSQL, you can use the to_char() function to output numbers in a given format. This includes appending the number with the ordinal indicator.
For example 1, 2, 3 becomes 1st, 2nd, 3rd.
Continue readingAdd the Ordinal Indicator to a Date in PostgreSQL
The PostgreSQL to_char() function provides us with the option of adding the ordinal number suffix to the result.
This means we can add the ordinal indicator when formatting dates.
For example, instead of outputting 10 Feb we could output 10th Feb.
Continue readingInstall Postgres.app on a Mac
The easiest way to install PostgreSQL on a Mac is by installing Postgres.app. Postgres.app is a full-featured PostgreSQL installation packaged as a standard Mac application.
You simply download it and drag it to your Applications folder, just like with any other application.
Continue readingHow to Format Numbers in PostgreSQL
When working with Postgres, you can use the to_char() function to output numbers in a given format.
The way it works is that you provide two arguments. The first argument is the number to be formatted. The second argument determines how it is formatted.
The return value is text.
Depending on your requirements, you might prefer to convert the number to the appropriate data type, for example using the cast() function.
Examples of both methods are below.
Continue readingPrepend a Plus/Minus Sign to a Number in PostgreSQL
In PostgreSQL, you can use the to_char() to output a number in a certain format, including with a plus or minus sign to indicate whether it’s a positive or negative number.
To do this, use one of S, MI, PL, or SG in your format string.
Another option is to use PR in order to enclose any negative values in angle brackets.
Get the Day Name from a Date in PostgreSQL
In PostgreSQL, you can get the day name from a date by using the to_char() function. This function returns a string based on the timestamp and the template pattern you provide as arguments..
Get the Short Month Name in PostgreSQL
In PostgreSQL, you can use the to_char() function to get the short month name from a date.
By “short month name” I mean the abbreviated month name, for example Jan, Feb, Mar, etc.
The to_char() function accepts two parameters; the date and the format string with which to format that date.