SQLite Tutorial
Advanced SQLite
SQLite Interfaces
SQLite Useful Resources
Selected Reading
- SQLite - DISTINCT Keyword
- SQLite - HAVING Clause
- SQLite - GROUP By Clause
- SQLite - ORDER By Clause
- SQLite - LIMIT Clause
- SQLite - GLOB Clause
- SQLite - LIKE Clause
- SQLite - DELETE Query
- SQLite - UPDATE Query
- SQLite - AND & OR Clauses
- SQLite - WHERE Clause
- SQLite - Expressions
- SQLite - Operators
- SQLite - SELECT Query
- SQLite - INSERT Query
- SQLite - DROP Table
- SQLite - CREATE Table
- SQLite - DETACH Database
- SQLite - ATTACH Database
- SQLite - CREATE Database
- SQLite - Data Type
- SQLite - Syntax
- SQLite - Commands
- SQLite - Installation
- SQLite - Overview
- SQLite - Home
Advanced SQLite
- SQLite - Useful Functions
- SQLite - Date & Time
- SQLite - VACUUM
- SQLite - EXPLAIN
- SQLite - Injection
- SQLite - AUTOINCREMENT
- SQLite - Subqueries
- SQLite - Transactions
- SQLite - Views
- SQLite - TRUNCATE Command
- SQLite - ALTER Command
- SQLite - INDEXED By Clause
- SQLite - Indexes
- SQLite - Triggers
- SQLite - ALIAS Syntax
- SQLite - NULL Values
- SQLite - UNIONS Clause
- SQLite - JOINS
- SQLite - Constraints
- SQLite - PRAGMA
SQLite Interfaces
SQLite Useful Resources
Selected Reading
- Who is Who
- Computer Glossary
- HR Interview Questions
- Effective Resume Writing
- Questions and Answers
- UPSC IAS Exams Notes
SQLite - Date & Time
SQLite - Date & Time
SQLite supports five date and time functions as follows −
| Sr.No. | Function | Example |
|---|---|---|
| 1 | date(timestring, modifiers...) | This returns the date in this format: YYYY-MM-DD |
| 2 | time(timestring, modifiers...) | This returns the time as HH:MM:SS |
| 3 | datetime(timestring, modifiers...) | This returns YYYY-MM-DD HH:MM:SS |
| 4 | jupanday(timestring, modifiers...) | This returns the number of days since noon in Greenwich on November 24, 4714 B.C. |
| 5 | strftime(timestring, modifiers...) | This returns the date formatted according to the format string specified as the first argument formatted as per formatters explained below. |
All the above five date and time functions take a time string as an argument. The time string is followed by zero or more modifiers. The strftime() function also takes a format string as its first argument. Following section will give you detail on different types of time strings and modifiers.
Time Strings
A time string can be in any of the following formats −
| Sr.No. | Time String | Example |
|---|---|---|
| 1 | YYYY-MM-DD | 2010-12-30 |
| 2 | YYYY-MM-DD HH:MM | 2010-12-30 12:10 |
| 3 | YYYY-MM-DD HH:MM:SS.SSS | 2010-12-30 12:10:04.100 |
| 4 | MM-DD-YYYY HH:MM | 30-12-2010 12:10 |
| 5 | HH:MM | 12:10 |
| 6 | YYYY-MM-DDTHH:MM | 2010-12-30 12:10 |
| 7 | HH:MM:SS | 12:10:01 |
| 8 | YYYYMMDD HHMMSS | 20101230 121001 |
| 9 | now | 2013-05-07 |
You can use the "T" as a pteral character separating the date and the time.
Modifiers
The time string can be followed by zero or more modifiers that will alter date and/or time returned by any of the above five functions. Modifiers are appped from the left to right.
Following modifers are available in SQLite −
NNN days
NNN hours
NNN minutes
NNN.NNNN seconds
NNN months
NNN years
start of month
start of year
start of day
weekday N
unixepoch
localtime
utc
Formatters
SQLite provides a very handy function strftime() to format any date and time. You can use the following substitutions to format your date and time.
| Substitution | Description | %d | Day of month, 01-31 | %f | Fractional seconds, SS.SSS | %H | Hour, 00-23 | %j | Day of year, 001-366 | %J | Jupan day number, DDDD.DDDD | %m | Month, 00-12 | %M | Minute, 00-59 | %s | Seconds since 1970-01-01 | %S | Seconds, 00-59 | %w | Day of week, 0-6 (0 is Sunday) | %W | Week of year, 01-53 | %Y | Year, YYYY | %% | % symbol |
|---|
Examples
Let s try various examples now using SQLite prompt. Following command computes the current date.
sqpte> SELECT date( now ); 2013-05-07
Following command computes the last day of the current month.
sqpte> SELECT date( now , start of month , +1 month , -1 day ); 2013-05-31
Following command computes the date and time for a given UNIX timestamp 1092941466.
sqpte> SELECT datetime(1092941466, unixepoch ); 2004-08-19 18:51:06
Following command computes the date and time for a given UNIX timestamp 1092941466 and compensate for your local timezone.
sqpte> SELECT datetime(1092941466, unixepoch , localtime ); 2004-08-19 13:51:06
Following command computes the current UNIX timestamp.
sqpte> SELECT strftime( %s , now ); 1393348134
Following command computes the number of days since the signing of the US Declaration of Independence.
sqpte> SELECT jupanday( now ) - jupanday( 1776-07-04 ); 86798.7094695023
Following command computes the number of seconds since a particular moment in 2004.
sqpte> SELECT strftime( %s , now ) - strftime( %s , 2004-01-01 02:34:56 ); 295001572
Following command computes the date of the first Tuesday in October for the current year.
sqpte> SELECT date( now , start of year , +9 months , weekday 2 ); 2013-10-01
Following command computes the time since the UNIX epoch in seconds (pke strftime( %s , now ) except includes fractional part).
sqpte> SELECT (jupanday( now ) - 2440587.5)*86400.0; 1367926077.12598
To convert between UTC and local time values when formatting a date, use the utc or localtime modifiers as follows −
sqpte> SELECT time( 12:00 , localtime ); 05:00:00
sqpte> SELECT time( 12:00 , utc ); 19:00:00Advertisements