In PostgreSQL, you can use the to_char() function to return dates in various formats.
One of the things you can do with this function is return the month portion of a date in roman numerals.
Continue readingTag: how to
How to Set the Interval Output Format in PostgreSQL
When working with the interval data type in PostgreSQL, you can change the way the interval output is formatted.
You have a choice of four formats that your intervals can be output in.
Continue readingCalculate the Age in Years in PostgreSQL
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 readingGet the Week Number from a Date in PostgreSQL
In PostgreSQL you can use the extract() function to get the week number from a date.
You can also use the date_part() function to do the same thing.
Get the Short Day Name in PostgreSQL
In PostgreSQL, you can use the to_char() function to get the short day name from a date.
By “short day name” I mean the abbreviated day name, for example Fri, Mon, etc.
The to_char() function accepts two parameters; the date and the format string with which to format that date.
Get the Day from a Date in PostgreSQL
In PostgreSQL you can use the extract() function to get the day from a date.
You can also use date_part() to do the same thing.
When extracting the day from a date, you need to specify what sense of the word “day” you mean. For example, “day of week”, “day of month”, “day of year”, etc.
Convert a Julian Day to a Date in PostgreSQL
Here are two ways to convert a given Julian day to its date value in PostgreSQL.
The first method directly inputs the Julian day into the date data type.
The second method uses the to_date() function to return the same result.
Julian day is the number of days since Monday, January 1, 4713 BC using the proleptic Julian calendar, which is November 24, 4714 BC, in the proleptic Gregorian calendar.
Continue readingDealing with Primary Key Conflicts when Inserting Data in SQLite
SQLite has a non-standard SQL extension clause called ON CONFLICT that enables us to specify how to deal with constraint conflicts.
In particular, the clause applies to UNIQUE, NOT NULL, CHECK, and PRIMARY KEY constraints.
This article provides examples of how this clause can be used to determine how to handle primary key constraint conflicts.
By “primary key constraint conflicts”, I mean when you try to insert a duplicate value into a primary key column. By default, when you try to do this, the operation will be aborted and SQLite will return an error.
But you can use the ON CONFLICT clause to change the way SQLite deals with these situations.
One option is to use this clause in the CREATE TABLE statement when creating the table. Doing that will determine how all INSERT operations are treated.
Another option is to use the clause on the INSERT statement whenever you try to insert data into the table. This allows you to take advantage of the clause even when the table wasn’t created with it. When you use this option, the syntax is different; you use OR instead of ON CONFLICT.
The examples on this page use the second option – I create the table without the ON CONFLICT clause, and I instead specify OR on the INSERT statement.
Convert a Date to Julian Day in PostgreSQL
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.
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 reading