Below is a list of valid modifiers for date and time functions in SQLite.
Continue readingCategory: Relational
Include Column Names in SQLite Query Results
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.
Continue readingBackup an SQLite Database
The SQLite command line shell provides the .backup dot command that enables you to quickly and easily back up a database.
To use this command, provide the name of the database you want to back up, and a file name for the backup file.
Continue readingClone an SQLite Database
You can use the SQLite .clone dot command to create a clone of the current database.
The way it works is quite simple. You simply use .clone newdb where newdb is the name of the new database you want created.
Export an Entire SQLite Database to an SQL File
The SQLite command line provides several methods for exporting or backing up an entire database. One of those involves the .dump dot command.
The .dump command enables you to convert a whole database to a single ASCII text file. In other words, it renders the whole database as SQL. The text file contains all the SQL statements required to create the tables, insert data, etc.
You can also use .dump to create a compressed file. I outline both of these methods below.
Export SQLite Query Results to a CSV File
When using the SQLite command line shell, you can export your query results to a CSV file by using the .mode dot command in conjunction with the .output or .once commands.
You can also use the .system command to open that file.
Automatically Open SQLite Query Results in a Text Editor
You can configure the SQLite command line interface to automatically open query results in a text editor.
When you do this, you have two options:
- Use a temporary file
- Use a permanent file (i.e. save it for later)
This article provides examples of both methods.
Continue readingAutomatically Open SQLite Query Results in Excel
The SQLite command line interface has a handy little feature where you can open your SQL query results in an Excel file.
When you run a query, instead of the query being output to your console as it normally would, it instead opens as an Excel spreadsheet (or LibreOffice, or whatever program your system uses to open CSV files).
You have two options when doing this:
- Use a temporary file
- Use a permanent file (i.e. save it for later)
This article covers both options.
Continue readingSave SQLite Query Results to a Text File
You can use the .output or .once dot commands to save your query results to a text file when using the SQLite CLI.
How to Format SQLite Query Results in TCL Mode
The SQLite command line interface allows you to format your query results in TCL mode. Doing this encloses all output in double quotes, including the column headers if you’ve specified those. Any internal double quotes are escaped with a backslash.
Continue reading