The SQLite command line interface provides us with several options for outputting our results in tabular format.
These can be set using the .mode command. You simply type .mode followed by the desired output format.
Below are examples of the various tabular modes available in SQLite.
The unix timestamp is the number of seconds since 1970-01-01 00:00:00 UTC. SQLite provides us with a couple of ways to get the unix timestamp.
SQLite has introduced the FORMAT() function, which can be used to format strings.
More precisely, it has actually just renamed its SQL PRINTF() function to FORMAT(). The reason is for better compatibility with other DBMSs. The original PRINTF() name is retained as an alias for backwards compatibility.
The FORMAT() function (or its naming) was introduced in SQLite 3.38.0, which was released on 22 February 2022.
SQLite’s DATE() function provides us with the ability to return the date of the first instance of a given day of a given month. Therefore, we can use it to return the first Monday of a given month. We can also use it to return the first Tuesday, Wednesday, Thursday, Friday, etc.
We can use DATETIME() if we want a datetime value to be returned.
If you have a Unix timestamp, you can use SQLite’s DATETIME() function with the unixepoch modifier to compute the actual date and time.
You can alternatively use the DATE() function if you only need the date to be returned. And it’s also possible to use the TIME() function to return just the time portion.
SQLite doesn’t have a SHOW TABLES statement like MySQL and MariaDB have, but it does have a similar command.
In SQLite, you can use the .tables command to show a list of tables. You can alternatively use the table_list pragma to do the job.
SQLite 3.38.0 was released on 2nd February 2022. With it came some enhancement to the date and time functions.
One of those enhancements is the addition of the UNIXEPOCH() function. This function returns a unix timestamp. That is, the number of seconds since 1970-01-01 00:00:00 UTC.
In SQLite, we can use the following method to calculate the last day of a given month.
We can base this either on the current date, or on some other specific date.
SQLite gives us the ability to return the date of the beginning of the month, based on a given date.
This means we can return the date of the first day of the current month, or the first day of the month based on a date that we specify.
This allows us to perform further calculations on the resulting date, like adding a given number of days to it.
In SQLite, we can use the DATE() function to subtract one or more years from a date.
For datetime values, we can use the DATETIME() function.