Sqlite

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 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 Group by Two Columns in SQL

Problem: You want to group your data by two columns so you can count some statistics. Example: In the order table, you have the columns order_date, product_id, customer_id, and number. You would like to count the number of products bought by each customer each day. The order table looks like this: order_dateproduct_idcustomer_idnumber 2023-11-25711 2023-11-251213 2023-11-265312 2023-11-26124 2023-11-26321 2023-11-261627 2023-11-26332 2023-11-27631 Solution: SELECT order_date, customer_id, SUM(number) AS products_number FROM order GROUP BY order_date, customer_id; 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 Find Rows with Minimum Value

Problem: You want to find rows which store the smallest numeric value in a column. Example: Our database has a table named weather with data in the following columns: id, city, and temperature. You want to find cities with the lowest temperature. idcitytemperature 1Houston23 2Atlanta20 3Boston15 4Cleveland15 5Dallas34 6Austin28 Solution: SELECT id, city, temperature FROM weather WHERE temperature = (SELECT MIN(temperature) FROM weather); Here’s the result:

How to Find Rows with Maximum Value

Problem: You want to find rows which store the largest numeric value in a given column. Example: Our database has a table named student with data in the following columns: id, first_name, last_name, and grade. You want to find the students who have the highest grades. idfirst_namelast_namegrade 1LisaJackson3 2GaryLarry5 3TomMichelin2 4MartinBarker2 5EllieBlack5 6MarySimpson4 Solution: SELECT id, first_name, last_name, grade FROM student WHERE grade = (SELECT MAX(grade) FROM student); Here’s the result:

How to Find Records with NULL in a Column

Problem: You want to find records with NULL in a column. Example: Our database has a table named children with data in four columns: id, first_name, middle_name, and last_name. idfirst_namemiddle_namelast_name 1JohnCarlJackson 2TomNULLThomson 3LisaAliceNULL 4AnneNULLSmith Let’s find the id, first name, and last name of children without a middle name (NULL in column middle_name). We use the IS NULL operator for this. Solution: SELECT id, first_name, last_name FROM children WHERE middle_name IS NULL; Here’s the result:

How to Filter Rows without NULL in a column

Problem: You want to find records without a NULL in a column. Example: Our database has a table named product with data in three columns: id, name, and price. idnameprice 1butterNULL 2milk2.35 3bread3.25 4cheeseNULL Let’s find the names and prices of products that have a price (without a NULL). Do this by using the IS NOT NULL operator.

How to Filter Records with Aggregate Function SUM

Problem: You need to filter groups of rows by the sum of a given column. Example: Our database has a table named company with data in the following columns: id, department, first_name, last_name, and salary. iddepartmentfirst_namelast_namesalary 1marketingLoraBrown2300 2financeJohnJackson3200 3marketingMichaelThomson1270 4productionTonyMiller6500 5productionSallyGreen2500 6financeOlivierBlack3450 7productionJenifferMichelin2800 8marketingJeremyLorson3600 9marketingLouisSmith4200 Let’s find the names of departments that have sums of salaries of its employees less than 7000.

How to Filter Records with Aggregate Function COUNT

Problem: You want to find groups of rows with a specific number of entries in a group. Example: Our database has a table named product with data in the following columns: id, name and category. idnamecategory 1sofafurniture 2glovesclothing 3T-Shirtclothing 4chairfurniture 5deskfurniture 6watchelectronics 7armchairfurniture 8skirtclothing 9radio receiverelectronics Let’s find the category of products with more than two entries.

How to Filter Records with Aggregate Function AVG

Problem: You want to filter groups of rows in by the average value of a given column. Example: Our database has a table named product with data in the following columns: id, name, store and price. idnamestoreprice 1milkGreen Shop2.34 2breadClark’s Grocery3.56 3breadSuper Market4.15 4milkSuper Market1.80 5breadGrocery Amanda2.26 6milkViolet Grocery3.45 7milkClark’s Grocery2.10 8breadViolet Grocery2.

How to Concatenate Strings in SQL

Problem: You want to join strings from two columns of a table into one. Example: Our database has a table named student with data in the following columns: id, first_name and last_name. idfirst_namelast_name 1LoraSmith 2EmilBrown 3AlexJackson 4MartinDavis Let’s append the first name to the last name of the student in one string. Use a space between each name.

What’s the Difference Between RANK and DENSE_RANK in SQL?

Problem: You want to compare the rankings produced by RANK and DENSE_RANK and add them as new columns to a table. Example: Our database has a table named sales_assistant with data in the following columns: id (primary key), first_name, last_name, month, and sold products. idfirst_namelast_namemonthsold products 1LisaBlack 52300 2MaryJacobs52400 3LisaBlack 62700 4MaryJacobs62700 5AlexSmith 62900 6MaryJacobs71200 7LisaBlack 71200 8AlexSmith 71000 Let’s display each sales assistant’s first and last name and number of sold products.

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.

How to Number Rows in SQL

Problem: You would like to give each row in the result table a separate number. Example: Our database has a table named furniture with data in the following columns: code (primary key) and name. codename 101bed 202sofa 333chair 123bookcase 235table 766desk The furniture table stores the name of pieces of furniture that we want to number.

How to Find the Minimum Value of a Column in SQL

Problem: You’d like to find the smallest numeric value in a column. Example: Our database has a table named employment with data in the following columns: id, first_name, last_name, department, and salary. idfirst_namelast_namedepartmentsalary 1EllieMartinesmarketing1200 2MartinJohnsonfinance2300 3MichaelJacobsproduction1100 4StephenKowalskimarketing4300 5StanleyMillermarketing3500 6JenyBrownfinance5000 7MargaretGreenmarketing1500 8LisaThomasproduction2800 Let’s find the lowest salary among all employees.

How to Find the Maximum Value of a Numeric Column in SQL

Problem: You’d like to find the maximum value of a numeric column. Example: Our database has a table named product with data in the following columns: id, name, year, and items. idnameyearitems 1bread roll2018345 2chocolate2017123 3butter201934 4bread roll2019456 5butter201856 6butter201778 7chocolate201987 8chocolate201876 Let’s find the maximum number of items sold over all years.

How to Find the Average of a Numeric Column in SQL

Problem: You’d like to calculate the average of numbers stored in a column. Example: Our database has a table named sale with data in the following columns: id, city, year, and amount. idcityyearamount 1Los Angeles20172345.50 2Chicago20181345.46 3Annandale2016900.56 4Annandale201723230.22 5Los Angeles201812456.20 6Chicago201789000.40 7Annandale201821005.77 8Chicago20162300.89 Let’s calculate the average sales, regardless of city or year.

How to Convert a String to Uppercase in SQL

Problem: You would like to convert a string to uppercase in SQL. Example: Our database has a table named questionnaire with data in the following columns: id, first_name, last_name, and favorite_car. idfirst_namelast_namefavorite_car 1AlanJacksonHonda Civic 2ElisaThomsonTOYOTA Camry 3MaryMartinesNissan rogue 4ChrisBrownford focus 5AdamSpringRam PICKUP Our table stores the make and model of the favorite car for each person who filled out our questionnaire.

How to Use LIKE in SQL

Problem: You need to search for specific patterns (certain combinations of letters or numbers) within text data in your database. We'll show you how to use the LIKE operator in SQL to: Find city names starting with S Find city names with exactly five characters Find city names starting with S and with o as the second-to-last character You'll also learn how to use LIKE and wildcard operators in SQL to find your own patterns in text data in your database.

How to Round Up a Number to the Nearest Integer in SQL

Problem: You want to round up a number to the nearest integer in SQL. Example: Our database has a table named rent with data in the following columns: id, city, area, and bikes_for_rent. idcityareabikes_for_rent 1Los Angeles1302.151000 2Phoenix1340.69500 3Fargo126.44101 Let’s show each city’s name along with the ratio of its area to the number of bikes for rent.

How to Round Numbers in SQL

Problem: You want to round a number to a specific number of decimal places in SQL. Example: Our database has a table named product with data in the following columns: id, name, and price_net. idnameprice_net 1bread2.34 2croissant1.22 3roll0.38 Suppose there’s a tax of 24% on each product, and you’d like to compute the gross price of each item (i.e., after taxes) and round the value to two decimal places.

How to Floor Numbers in SQL

Problem: You want to round a number down to the nearest integer. Example: Our database has a table named employee with data in the following columns: id, first_name, last_name, and hours_worked (for the current month). idfirst_namelast_namehours_worked 1AlanWatson95 2LisaBlack225 3LauraWilliams104 Let’s show the first and last name of each employee along with the number of days they worked.

How to Add Rankings with DENSE_RANK() in SQL

Problem: You want to add a ranking position to rows consecutively, even if the rows have the same values. Example: Our database has a table named competition with data in the following columns: id (primary key), first_name, last_name, and score. idfirst_namelast_namescore 11JohnThomas345 14MaryJohnson222 16LisaBrown154 23AlanBlake222 32ChrisTaylor154 Let’s display all details about students: their last name, first name, and scores sorted by score in descending order.

How to Sum Values of a Column in SQL?

Problem: You’d like to sum the values of a column. Example: Our database has a table named game with data in the following columns: id, player, and score. idplayerscore 1John134 2Tom 146 3Lucy20 4Tom 118 5Tom 102 6Lucy90 7Lucy34 8John122 Let’s find the total score obtained by all players. Solution: SELECT SUM(score) as sum_score FROM game; Here’s the result:

How to Replace Part of a String in SQL

Problem: You’d like to replace part of a string with another string. Example: Our database has a table named investor with data in the following columns: id, company, and phone. idcompanyphone 1Big Market123–300-400 3The Sunny Restaurant123–222-456 4My Bank123-345-400 We’d like to change the phone number format for each company by replacing the hyphen character with a space.

How to Count the Number of Rows in a Table in SQL

Problem: You’d like to determine how many rows a table has. Example: Our database has a table named pet with data in the following columns: id, eID (electronic identifier), and name. ideIDname 123456sparky 223457mily 3NULLlessy 4NULLcarl 534545maggy Let’s count all rows in the table. Solution: COUNT(*) counts the total number of rows in the table:

How to Order by Count in SQL?

Problem: You aggregated data into groups, but you want to sort the records in descending order by the number of elements in the groups. Example: Our database has a table named user with data in the following columns: id, first_name, last_name, and country. idfirst_namelast_namecountry 1LisaWilliamsEngland 2GaryAndersPoland 3TomWilliamsPoland 4MichaelBrownFrance 5SusanSmithUSA 6AnneJonesUSA 7EllieMillerPoland Let’s create a report on our users.

How to Order By Two Columns in SQL?

Problem: You need to display records from a given table sorted by two columns. Example: Our database has a table named employee with the following columns: id, first_name, last_name, and salary. idfirst_namelast_namesalary 1LisaUlman3000 2AdaMuller2400 3ThomasGreen2400 4MichaelMuller3000 5MaryGreen2400 Let’s display all information for each employee but sort the records according to salary in descending order first and then by last name in ascending order.

How to Multiply Two Columns in SQL

Problem: You want to multiply values from two columns of a table. Example: Our database has a table named purchase with data in the following columns: id, name, price, quantity, and discount_id. idnamepricequantitydiscount_id 1pen731 2notebook582 3rubber1131 4pencil case2423 Let’s multiply the price by the quantity of the products to find out how much you paid for each item in your order.

How to Join on Multiple Columns

Problem: You want to join tables on multiple columns by using a primary compound key in one table and a foreign compound key in another. Example: Our database has three tables named student, enrollment, and payment. The student table has data in the following columns: id (primary key), first_name, and last_name. idfirst_namelast_name 1EllieWillson 2TomBrown 3SandraMiller The enrollment table has data in the following columns: primary key (student_id and course_code), is_active, and start_date.

How to Find Duplicate Rows in SQL?

Problem: You have duplicate rows in your table, with only the IDs being unique. How do you find those duplicate entries? Example: Our database has a table named product with data in the following columns: id, name, and category. idnamecategory 1steakmeat 2cakesweets 3steakmeat 4porkmeat 5cakesweets 6cakesweets Let’s find duplicate names and categories of products.

How to Eliminate Duplicate Rows in SQL

Problem: You’d like to eliminate any duplicate rows from the result set of a query so that each row appears only once. Example: Our database has a table named clothes with data in the following columns: id, name, color, and year_produced. idnamecoloryear_produced 1T-shirtyellow2015 2jacketblue2016 3jeansblack2015 4jacketblue2015 5jacketgreen2016 6jacketyellow2017 7hatyellow2017 Let’s get the non-repeated names and colors of clothes produced before 2017.

How to Count Distinct Values in SQL

Problem: You’d like to count how many different non-NULL values there are in a given column. Example: Our database has a table named customer with data in the following columns: id, first_name, last_name, and city. idfirst_namelast_namecity 1JohnWilliamsChicago 2TomBrownAustin 3LucyMillerChicago 4EllieSmithDallas 5BrianJonesAustin 6AllanDavisNULL Let’s find the number of different (and non-NULL) cities.

How to Order Alphabetically in SQL

Problem: You want to display records from a table in alphabetical or reverse-alphabetical order according to given column. Example: Our database has a table named customer. The customer table contains data in the id, first_name, and last_name columns. id first_name last_name 1 Susan Thomas 2 John Michael 3 Tom Muller Let’s display each customer’s information, sorted in ascending order by their last name.

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: