In PostgreSQL, the localtime function returns the local time of the day, without the time zone offset.
If you need the time zone offset, use current_time() instead.
How current_time Works in PostgreSQL
In PostgreSQL, the current_time function returns the current time, including the time zone offset.
The time returned is based on the start time of the current transaction.
Continue readingHow current_date Works in PostgreSQL
In PostgreSQL, the current_date function returns the current date.
How date_trunc() Works in PostgreSQL
In PostgreSQL, the date_trunc() function truncates a date/time value to a specified precision.
It can also truncate the value to a specified precision in a specified time zone.
You could think of it as a date version of the trunc() function (which truncates numbers).
How the to_number() Function Works in PostgreSQL
In PostgreSQL, you can use the to_number() function to convert a string to a numeric value.
More specifically, it converts the string representation of a number to a numeric value.
For example, if you have $1,234.50 as a string, you can use to_number() to convert that to an actual number that uses the numeric data type.
Dealing 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.
How Atanh() Works in PostgreSQL
In PostgreSQL, atanh() is a hyperbolic function that returns the inverse hyperbolic tangent of its argument.
The argument and return value are provided as a double precision value.
Continue readingHow Acosh() Works in PostgreSQL
In PostgreSQL, acosh() is a hyperbolic function that returns the inverse hyperbolic cosine of its argument.
The argument and return value are provided as a double precision value.
Continue readingHow Asinh() Works in PostgreSQL
In PostgreSQL, asinh() is a hyperbolic function that returns the inverse hyperbolic sine of its argument.
The argument and return value are provided as a double precision value.
Continue reading