Sqlite

How to Divide Columns in SQL Server, PostgreSQL, or SQLite

Problem You want to divide one column by another in SQL Server, PostgreSQL, or SQLite. Example An online store has an orders table with data in the columns order_id, total_order_payment, and item_count. order_idtotal_order_paymentitem_count 124 2154 3562 Let’s say we want to extract the average cost per item for each order, i.e., the total payment for the order divided by the item count.

How to Group by Month in SQLite

Problem You'd like to group records by month in an SQLite database. Example Our database has a table named notebooks with data in the columns id, color, and production_timestamp. idcolorproduction_timestamp 1Burgundy2022-02-01 11:45:23 2Gray2022-02-01 11:46:13 3Orange2022-01-22 17:22:05 Solution You can use the STRFTIME() function to get the month and year value from the date. You can also use it to group these values.

How to Get Day Names in SQLite

Problem You want to extract the name of the day of the week from a date in SQLite. Solution 1 To extract the day name from the date, use the strftime() function. The first parameter is the desired output and the second is the date. To extract the day name, the first parameter should be %w. It outputs a number from 0 (Sunday) to 6 (Saturday). Here’s an example:

How to Limit Results in MySQL, PostgreSQL and SQLite

Problem You want to limit the number of rows resulting from a query in MySQL, PostgreSQL, or SQLite. Example In the exam table, there are names of the students with the results of the exam. nameexam_result Janet Morgen9 Taya Bain11 Anne Johnson11 Josh Kaur10 Ellen Thornton8 You want to get the three rows with the best exam results.

Kateryna Koidan

SQL Date and Time Functions in 5 Popular SQL Dialects

Are you confused by all the date and time functions used across different SQL dialects? In this article, I summarize the date and time data types used in PostgreSQL, Oracle, SQLite, MySQL, and T-SQL. I also provide examples with the key SQL date and time functions used across these dialects. It’s time to become date and time gurus! Do you want to calculate how often employees are running late for work?

How to Order by Month Name in SQLite

Problem: You want to sort the rows by month number, given month names (you want January to be shown first, December last). Example: The birthday table contains two columns: name and birthday_month. The months are given in names, not in numbers. namebirthday_month Ronan TishaJanuary Angie JuliaApril Narelle DillanApril Purdie CaseyJanuary Donna NellNULL Blaze GraemeOctober You want to sort the rows by birthday_month.

How to Order by Date in SQLite

Problem: You want to sort the rows by date in SQLite database. Example 1: The exam table has two columns, subject and exam_date. subjectexam_date Mathematics2023-12-19 English2024-01-08 Science2024-01-05 Health2024-01-05 ArtNULL You want to sort the rows by exam_date. Solution: SELECT * FROM exam ORDER BY exam_date; The result looks like this.

How to Extract a Substring From a String in Oracle or SQLite

Problem You have a column of strings, and you'd like to get substrings from them. Example 1 In the emails table, there is an email column. You'd like to display the first seven characters of each email. The table looks like this: email jake99@gmail.com tamarablack@zoho.com notine@yahoo.fr jessica1995@onet.pl Solution 1 SELECT email, SUBSTR(email, 1, 7) AS substring FROM emails; The result is:

How to Find the Difference Between Two Timestamps in SQLite

Problem You have two columns of the type timestamp and you want to calculate the difference between them. Example In the travel table, there are three columns: id, departure, and arrival. You'd like to calculate the difference between the arrival and the departure. The travel table looks like this: iddeparturearrival 12018-03-25 12:00:002018-04-05 07:30:00 22019-09-12 15:50:002019-10-23 10:30:30 32018-07-14 16:15:002018-07-14 20:40:30 42018-01-05 08:35:002019-01-08 14:00:00 Solution 1: Difference in Days SELECT id, departure, arrival, JULIANDAY(arrival) - JULIANDAY(departure) AS difference FROM travel; The result is:

How to Calculate the Difference Between Two Dates in SQLite

Problem You have two columns of the date type in SQLite database and you want to calculate the difference between them. Example In the travel table, there are three columns: id, departure, and arrival. You'd like to calculate the difference between arrival and departure and the number of days from arrival to departure inclusively. The travel table looks like this: iddeparturearrival 12018-03-252018-04-05 22019-09-122019-09-23 32018-07-142018-07-14 42018-01-052018-01-08 Solution SELECT id, departure, arrival, JULIANDAY(arrival) - JULIANDAY(departure) AS date_difference, JULIANDAY(arrival) - JULIANDAY(departure) + 1 AS days_inclusive FROM travel; The result is:

How to Get Yesterday’s Date in SQLite

Problem You would like to display yesterday's date (without time) in an SQLite database. Solution 1 SELECT DATE('now','-1 day') AS yesterday_date; Assuming today is 2023-09-24, the result is: yesterday_date 2023-09-23 Discussion To get yesterday's date, you need to subtract one day from today. Use DATE('now') to get today's date. In SQLite, you can subtract or add any number of days, months, etc.

How to Get the Current Time in SQLite

Problem You’d like to get the current time in an SQLite database. Solution We’ll use the function TIME() to get the current time. SELECT TIME('now'); Here’s the result of the query: 10:56:12 Discussion The SQLite function TIME() returns the current time as a text value. It uses the hh:mm:ss format, where hh is a 2-digit hour, mm is a 2-digit minute, and ss is a 2-digit second.

How to Get the Current Date in SQLite

Problem You’d like to get the current date in an SQLite database. Solution We’ll use the function DATE() to get the current date. SELECT DATE('now'); Here’s the result of the query: 2019-10-15 Discussion The SQLite function DATE('now') returns the current date as a text value. It uses the YYYY-MM-DD format, where YYYY is a 4-digit year, MM is a 2-digit month, and DD is a 2-digit day of the month.

How to Format a Datetime in SQLite

Problem You want to format a datetime column or value in SQLite. Example Our database has a table named ticket with data in the columns passenger_ID, train_number, and sale_datetime. passenger_IDtrain_numbersale_datetime 245NX45052019-10-17 12:56:30 127RG34032019-10-17 11:45:23 567FH45052019-10-18 12:40:32 230AP67002019-10-18 13:20:20 118BB34032019-10-18 13:13:13 For each ticket sale, let’s get the passenger ID, train number, and the sale date and time.

How to Add a Month to a Date in SQLite

Problem You would like to add one month to a given date in an SQLite database. Example Our database has a table named production with data in the columns id, product, and start_date. idproductstart_date 1desk2019-08-17 2armchair2019-07-17 3sofa2019-10-31 Products have a new production start date that’s one month later than listed above. Let’s get the products’ names and their new start date.

How to Trim a String in SQLite

Problem: You’d like to remove spaces or a specific characters from the beginning and end of a string in SQLite. Example: Our database has a table named category with data in two columns: id and name. idname 1' sweets ' 2'meat ' 3' vegetables' Let’s trim the name of each category of products to remove the unnecessary space at the beginning and end.

Agnieszka Kozubek-Krycuń

MySQL's group_concat Equivalents in PostgreSQL, Oracle, DB2, HSQLDB, and SQLite

The GROUP_CONCAT() function in MySQL MySQL has a very handy function which concatenates strings from a group into one string. For example, let's take a look at the children table with data about parents' and children's names. if (typeof VertabeloEmbededObject === 'undefined') {var VertabeloEmbededObject = "loading";var s=document.createElement("script");s.setAttribute("type","text/javascript");s.setAttribute("src", "https://my.vertabelo.com/js/public-model/v1/api.js");(document.getElementsByTagName("head")[0] || document.documentElement ).appendChild(s);} parent_name child_name John Tom Michael Sylvie John Anna Michael Sophie To get the names of children of each person as a comma-separated string, you use the GROUP_CONCAT() function as follows: