SQLite Date query

sqlite> .open E:/sqlite3_mgmt/my_date.db

Connect to database or create database

#### FOR SQLite Database ######
// This will create the database if not exists in the same location where the script is running.
// For a different location give the path details. 
// use the SQL dump to create table inside the database. 
//$my_conn = new PDO('sqlite:D:\\sqlite-data\\my_date.db');// different path
$my_conn = new PDO('sqlite:'.dirname(__FILE__).'/my_date.db'); // same location
$my_conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
#### end of SQLite database connection #####

$sql= " "; // Paste the copied data ( SQL dump ) taken from above link. 
try {
$my_conn->exec($sql);
}
catch (PDOException $e) {
    echo "Failed to create table: " . $e->getMessage();
}

PHP Script to connect and get data from SQLite database table

<?Php
$query="SELECT record_id,my_dt,month_year FROM table_dt WHERE my_dt=DATE('now')";

echo "<table><tr><th>record_id</th><th>Date</th>
	<th>month-year</th></tr>";
foreach($my_conn->query($query) as $row){
echo "<tr><td>$row[record_id]</td><td>$row[my_dt]</td>
	<td>$row[month_year]</td></tr>";
}
echo "</table>";
?>

sqlite> SELECT * FROM table_dt WHERE my_dt=DATE('now');

SELECT * FROM table_dt WHERE my_dt=DATE('now','start of month','+1 month','-1 day');

SELECT * FROM table_dt WHERE my_dt 
	BETWEEN DATE('now','-3 day') AND DATE('now');

0|2023-11-07|Nov-2023
1|2023-11-06|Nov-2023
2|2023-11-05|Nov-2023
3|2023-11-04|Nov-2023

SELECT * FROM table_dt WHERE my_dt 
	BETWEEN DATE('now','-10 day') AND DATE('now','-3 day');

3|2023-11-04|Nov-2023
4|2023-11-03|Nov-2023
5|2023-11-02|Nov-2023
6|2023-11-01|Nov-2023
7|2023-10-31|Oct-2023
8|2023-10-30|Oct-2023
9|2023-10-29|Oct-2023
10|2023-10-28|Oct-2023

Present Month Year or Day

SELECT * FROM table_dt WHERE STRFTIME('%m',my_dt)=STRFTIME('%m','now');

SELECT * FROM table_dt WHERE STRFTIME('%Y-%m',my_dt)=STRFTIME('%Y-%m','now');

SELECT * FROM table_dt WHERE STRFTIME('%Y',my_dt)=STRFTIME('%Y','now');

Previous Year , Month and day

SELECT * FROM table_dt WHERE STRFTIME('%Y',my_dt)=STRFTIME('%Y','now','-1 year');

SELECT * FROM table_dt WHERE STRFTIME('%Y-%m','now','-1 year') = STRFTIME('%Y-%m',my_dt);

SELECT * FROM table_dt WHERE STRFTIME('%Y-%m-%d','now','-1 year') = STRFTIME('%Y-%m-%d',my_dt);

SELECT * from table_dt WHERE STRFTIME('%Y-%m-%d','now','-1 month') = STRFTIME('%Y-%m-%d',my_dt);

Matching Weekday of the year

SELECT * FROM table_dt WHERE STRFTIME('%W','now')=STRFTIME('%W',my_dt);

SELECT * FROM table_dt WHERE STRFTIME('%W-%Y','now')=STRFTIME('%W-%Y',my_dt);

SELECT * FROM table_dt WHERE STRFTIME('%W-%Y','now','weekday 1')=STRFTIME('%W-%Y',my_dt);

SELECT * FROM table_dt WHERE STRFTIME('%Y-%m-%w','now')=STRFTIME('%Y-%m-%w',my_dt);

SQLite Supported Date and Time Functions

SQLite provides several built-in functions to work with date and time:

• DATE(): Returns the date in YYYY-MM-DD format.

SELECT DATE('now');

• TIME(): Returns the time in HH:MM:SS format.

SELECT TIME('now');

• DATETIME(): Returns the date and time in YYYY-MM-DD HH:MM:SS format.

SELECT DATETIME('now');

• JULIANDAY(): Returns the Julian day number.

SELECT JULIANDAY('now');

• STRFTIME(): Formats the date/time based on a format string.

SELECT STRFTIME('%Y-%m-%d %H:%M:%S', 'now');

Formatting Options Using STRFTIME() in SQLite

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: