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

1. Create Database ( testdb ) if it does not exists
2. Change context to database ( testdb)
3. Start a new transaction using “start transaction”
4. Drop test table testdb.CalendarDay if it exists
5. Create table testdb.CalendarDay
6. Table Structure
   • Columns
     • day
   • Index
     • Primary Key
7. Using insert statement, add data
8. 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
9. 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.