SQL Tip 1 Mình giới thiệu cách sử dụng hàm QUERY (SQL) với Google Sheet Với hàm query, bạn có thể viết những dòng query đầu tiên mà không cần biết code hay cài đặt phần mềm. Video dưới đây mình sẽ demo cách sử dụng SQL với Google Sheet. Nếu bạn muốn học thêm về SQL có thể tham gia khóa học SQL căn bản của mình. Link đăng kí tại đây: https://lnkd.in/gi72_WVA ------ Khóa học SQL căn bản dành cho các bạn muốn tìm hiểu về ngôn ngữ SQL (Structure Query Language). Nội dung của khóa được thiết kế riêng giúp các bạn non-tech có thể dễ dàng tiếp cận với SQL. Đặc biệt, sau mỗi khóa học mình sẽ dành 1 buổi mentoring 1-1 cho mỗi bạn học viên để cùng trao đổi về mong muốn cũng như career path các bạn muốn hướng tới. Thông tin về khóa học : https://lnkd.in/dmpWaJ3B

sqltip

google

Finally, note that this works whether you have entries marked by date or entries marked by timestamp, which contains both the date and time of entry.

Getting entries filed between two dates

You can also query for entries filed between two dates. Using our previous sheet, we want to get entries filed between 2020-07-05 and 2020-07-10. To do so, we make the following query:

select * where A > date '2020-07-05' AND A < date '2020-07-10'

The whole formula is:

\=query(loaforders!A2:G46,"select * where A > date '2020-07-05' AND A < date '2020-07-10'")

And the result is:

Result of query for entries from 2020-07-05 to 2020-07-10

Small tweak to include and/or exclude the given dates

As you have noticed in the previous examples, using the greater than and less than signs can exclude the final date from the results. For our example of entries between the two dates, the entries for the date 2020-07-05 were included while the entries for the date 2020-07-10 were excluded. We can illustrate that by using a timeline diagram:

How Google Sheets interpret the dates, with the original query that we used

By default, a date is assumed to have a time of 00:00:00, which is midnight. Therefore, when running this query, you will end up having entries from 2020-07-05 00:00:00 to 2020-07-10 00:00:00. This means that, using the original query, the entries that would have been marked 2020-07-10 won’t be included in the query results. To include those entries, you can simply adjust it to the next date, which is 2020-07-11 00:00:00. The query would then be:

select * where A > date '2020-07-05' AND A < date '2020-07-11'

The whole formula is:

\=query(loaforders!A2:G46,"select * where A > date '2020-07-05' AND A < date '2020-07-11'")

And the result is:

Result of query for entries from 2020-07-05 to 2020-07-10, adjusted to include those with date 2020-07-10

The DATETIME clause

We can also include the time in querying entries. For that, we will use the DATETIME function. It is used to indicate the data as containing both the date and the time. The data should have the following format:

YYYY-MM-DD HH:mm:ss

To show how it works, let’s look at an example. We wish to query the sheet of entries made before 11 AM and after 3 PM. For querying entries made before 11 AM, here is the actual query:

select * where A < datetime '2020-06-29 11:00:00'

Where the actual entry contains a date and a time. The whole formula is:

\=query(source!A2:H46,"select * where A < datetime '2020-06-29 11:00:00'")

And the result is:

Result of query for entries made before 2020-06-29 11:00:00

For querying entries made after 3 PM, here is the actual query:

select * where A > datetime '2020-06-29 15:00:00'

The whole formula is:

\=query(source!A2:H46,"select * where A > datetime '2020-06-29 15:00:00'")

And the result is:

Result of query for entries made after 2020-06-29 15:00:00

For querying entries between 11 AM and 1 PM on 2020-06-29, here is the actual query:

select * where A > datetime '2020-06-29 11:00:00' and A < datetime '2020-06-29 13:00:00'

The whole formula is:

\=query(source!A2:H46,"select * where A > datetime '2020-06-29 11:00:00' and A < datetime '2020-06-29 13:00:00'")

And the result is:

Result of query for entries made between 2020-06-29 11 AM and 1 PM

Summary of query forms

Each of the query forms that we discussed are summarized below. We modified the query forms a little: the clauses are all caps while the information is in small caps. The information you need to input will be clearly named as such: