SQLite – Page 28

How the JulianDay() Function Works in SQLite

January 28, 2021May 3, 2020 by Ian

The SQLite julianday() function returns the number of days since noon in Greenwich on November 24, 4714 B.C. (using the Proleptic Gregorian calendar).

If using the proleptic Julian calendar, this is Monday, January 1, 4713 BC.

Julian day is the continuous count of days since the beginning of the Julian Period. It’s typically used by astronomers, software, etc to calculate elapsed days between two events.

To use this function, you need to provide a time string, plus any (optional) modifiers. A modifier allows you to change the date, such as add a number of days, set it to local time, etc

How the DateTime() Function Works in SQLite

January 28, 2021May 2, 2020 by Ian

The SQLite datetime() function enables you to return a date and time value based on a time string and any modifiers.

It returns the date in this format: YYYY-MM-DD HH:MM:SS

To use this function, you need to provide a time string, plus any (optional) modifiers. A modifier allows you to change the date, such as add a number of days, set it to local time, etc

How the Time() Function Works in SQLite

January 28, 2021May 1, 2020 by Ian

SQLite has a time() function that enables you to return a time value based on a time string and any modifiers.

It returns the time in this format: HH:MM:SS

To use this function, you need to provide a time string, plus any (optional) modifiers. A modifier allows you to change the time, such as add a number of hours, set it to local time, etc

How the Date() Function Works in SQLite

January 28, 2021April 30, 2020 by Ian

SQLite has a date() function that enables you to return a date value based on a time string and any modifiers.

It returns the date in this format: YYYY-MM-DD

To use this function, you need to provide a time string, plus any (optional) modifiers. A modifier allows you to change the date, such as add a number of days, set it to local time, etc

Valid Format String Substitutions for the SQLite Strftime() Function

January 28, 2021April 29, 2020 by Ian

The table below contains the valid format string substitutions that you can use with the strftime() function in SQLite.

Valid Time String Formats for SQLite Date/Time Functions

January 28, 2021April 28, 2020 by Ian

Below is a list of valid time string formats that you can use in any date and time function in SQLite.

3 Ways to Generate a CREATE TABLE Script from an Existing Table in SQLite

June 2, 2022April 28, 2020 by Ian

This article contains three ways to generate an SQL script from an existing table in SQLite.

All three methods generate the CREATE TABLE script, but the last method also generates the INSERT statements for inserting the data.

Return a List of Databases in SQLite

May 29, 2020April 27, 2020 by Ian

When using the SQLite command line interface, you can use the .databases command to return a list of attached databases. More specifically, it returns the names and file names/location of each attached database.

You can also use the PRAGMA database_list statement to return a list of databases attached to the current database connection.

Valid Modifiers for SQLite Date/Time Functions

January 28, 2021April 26, 2020 by Ian

Below is a list of valid modifiers for date and time functions in SQLite.

Include Column Names in SQLite Query Results

May 28, 2020April 26, 2020 by Ian

By default, the SQLite command line interface displays query results using a pipe-separated list, without column headers.

This might be fine if you’re using the results in an application that doesn’t need the column names, but if you’re just a human, it can make it hard to read the data, especially if there are lots of columns.

Fortunately, there’s a quick way to present the column names in your query results.

Actually, there are at least a couple of ways to do this, and I explain them in this article.