Background
Here I am trying to follow a blog post, but not quite sure whether all of its thoughts are still true.
I have access to a MySQL instance and so let us see if I am able to follow more closely.
BTW, I might have a later version of MySQL.
Code
Outline
- Create Database ( testdb ) if it does not exists
- Change context to database ( testdb)
- Start a new transaction using “start transaction”
- Drop test table testdb.CalendarDay if it exists
- Create table testdb.CalendarDay
- Table Structure
- Columns
- day
- Index
- Primary Key
- Columns
- Using insert statement, add data
- Fetch data using formating our qualifier a bit differently each time
- Query 1
- Less Than or Equal
- Equal or Greater Than
- Query 2
- Between
- Hard-Coded Values
- Query 3
- Between
- Hard-Coded Values and Interval Adding Days
- Query 1
- Drop-Table if it exists
SQL
create database if not exists testdb; use testdb; start transaction; drop table if exists testdb.calendarDay; create table testdb.calendarDay ( day date , primary key ( day ) ) ; insert into testdb.calendarDay values ('2020-01-01') , ('2020-01-02') , ('2020-01-03') , ('2020-01-04') , ('2020-01-05') , ('2020-01-06') , ('2020-01-07') , ('2020-01-08') , ('2020-01-09') , ('2020-01-10') ; select 'sample using >= and <=' as 'source' , tblCD.day from testdb.calendarDay tblCD where ( ( day >= '2020-01-06' ) and ( day <= '2020-01-08' ) ) ; select 'sample using between' as 'source' , tblCD.day from testdb.calendarDay tblCD where day between '2020-01-06' and '2020-01-08' ; select 'sample using between and interval' as 'source' , tblCD.day from testdb.calendarDay tblCD where day between '2020-01-06' and '2020-01-06' + INTERVAL 2 DAY ; drop table if exists testdb.calendarDay; rollback;
Source Code Control
GitHub
Gist
DanielAdeniji/mysql.datatype.date.queryingData.sql
Link
Summary
Between Clause
In a query, the between clause is inclusive, that is the two arguments are included in the result set.
If you do not want either of the two arguments, please opt for greater / less than, rather than between.
INTERVAL Clause
Though the Interval N duration, as in + INTERVAL 2 DAYS, does not appear to be ANSI SQL, it can be quite useful and it is easy enough to understand its goal.
In terms of code portability, one should be able to find counterparts within other database platforms.