# https://www.plus2net.com/python/download/sqlite-date-query.html
import sqlite3
my_conn = sqlite3.connect('test2_db') # Change your database name here
print("Connected to database successfully")
# todays record
query="SELECT * FROM dt_table WHERE date=date('now')"
r_set=my_conn.execute(query)
for row in r_set:
print(row)
# yesterday record
query="SELECT * FROM dt_table WHERE date=date('now','-1 day')"
r_set=my_conn.execute(query)
for row in r_set:
print(row)
# tomorrow record
query="SELECT * FROM dt_table WHERE date=date('now','1 day')"
r_set=my_conn.execute(query)
for row in r_set:
print(row)
# previous 5 days record with future records
query="SELECT * FROM dt_table WHERE date>=date('now','-5 day')"
r_set=my_conn.execute(query)
for row in r_set:
print(row)
# previous 2 days record ( upto today only), no future dates included.
query="SELECT * FROM dt_table WHERE date between date('now','-2 day') AND date('now')"
r_set=my_conn.execute(query)
for row in r_set:
print(row)
# Display all records of next 3 days ( no previous dates )
query="SELECT * FROM dt_table WHERE date between date('now') AND date('now','3 day')"
r_set=my_conn.execute(query)
for row in r_set:
print(row)
# previous 10 days to previous 5 days record .
query="SELECT * FROM dt_table WHERE date between date('now','-10 day') AND date('now','-5 day')"
r_set=my_conn.execute(query)
for row in r_set:
print(row)
# Display all records of next 5 days to next 10 days
query="SELECT * FROM dt_table WHERE date between date('now','5 day') AND date('now','10 day')"
r_set=my_conn.execute(query)
for row in r_set:
print(row)
# all records of current calendar month
query="SELECT * FROM dt_table WHERE strftime('%m',date) = strftime('%m','now')"
r_set=my_conn.execute(query)
for row in r_set:
print(row)
# all records of previous calendar month
query="SELECT * FROM dt_table WHERE strftime('%m',date) = strftime('%m','now','-1 month')"
r_set=my_conn.execute(query)
for row in r_set:
print(row)
# all records of current calendar month till today ( 1st day till today)
query="SELECT * FROM dt_table WHERE date between strftime('%Y-%m-01','now') and strftime('%Y-%m-%d','now')"
r_set=my_conn.execute(query)
for row in r_set:
print(row)
# all records of next calendar month
query="SELECT * FROM dt_table WHERE strftime('%m',date) = strftime('%m','now','1 month')"
r_set=my_conn.execute(query)
for row in r_set:
print(row)
# all records of current year
query="SELECT * FROM dt_table WHERE strftime('%Y',date) = strftime('%Y','now')"
r_set=my_conn.execute(query)
for row in r_set:
print(row)
# all records of present week
query="SELECT * FROM dt_table WHERE strftime('%W',date) = strftime('%W','now') "
r_set=my_conn.execute(query)
for row in r_set:
print(row)
# all records of next week
query="SELECT * FROM dt_table WHERE CAST(strftime('%W',date) AS INTEGER) = CAST(strftime('%W','now') AS INTEGER) + 1 "
r_set=my_conn.execute(query)
for row in r_set:
print(row)
# all records of first week of the year
query="SELECT * FROM dt_table WHERE CAST(strftime('%W',date) AS INTEGER) = 1 "
r_set=my_conn.execute(query)
for row in r_set:
print(row)
# all records of previous week ( add year if other years are included.)
query="SELECT * FROM dt_table WHERE CAST(strftime('%W',date) AS INTEGER) = CAST(strftime('%W','now') AS INTEGER) - 1 "
r_set=my_conn.execute(query)
for row in r_set:
print(row)
query="SELECT * FROM dt_table WHERE strftime('%Y', date)=strftime('%Y','now') "
r_set=my_conn.execute(query)
for row in r_set:
print(row)
# all working days of current week , add year in condition if required
query="SELECT * FROM dt_table WHERE strftime('%W', date)=strftime('%W','now') and \
CAST(strftime('%w',date) AS INTEGER) BETWEEN 1 AND 5"
r_set=my_conn.execute(query)
for row in r_set:
print(row)
# all working days upto today of current week , add year in condition if required
query="SELECT * FROM dt_table WHERE strftime('%W', date)=strftime('%W','now') and \
CAST(strftime('%w',date) AS INTEGER) BETWEEN 1 AND CAST(strftime('%w','now') AS INTEGER) "
r_set=my_conn.execute(query)
for row in r_set:
print(row)