SQLite underpins five date and time works 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 | julianday(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 over five date and time capacities take a period string as a contention. The time string is trailed by nothing or more modifiers. The strftime() work likewise takes an organization string as its first contention. Following area will give you detail on various kinds of time strings and modifiers.
Time Strings
A period string can be in any of the accompanying organizations −
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 utilize the "T" as a strict character isolating the date and the time.
Modifiers
The time string can be trailed by nothing or more modifiers that will change date as well as time returned by any of the over five capacities. Modifiers are applied from the left to right.
Following modifers are accessible in SQLite −
- NNN days
- NNN hours
- NNN minutes
- NNN.NNNN seconds
- NNN months
- NNN years
- beginning of month
- beginning of year
- beginning of day
- work day N
- unixepoch
- localtime
- utc
Formatters
SQLite gives a helpful capacity strftime() to arrange any date and time. You can utilize the accompanying replacements to arrange 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 | Julian 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
How about we attempt different models presently utilizing SQLite brief. Following order figures the current date.
sqlite> SELECT date('now');
2013-05-07
Following order figures the most recent day of the current month.
sqlite> SELECT date('now','start of month','+1 month','-1 day');
2013-05-31
Following order figures the date and time for a given UNIX timestamp 1092941466.
sqlite> SELECT datetime(1092941466, 'unixepoch');
2004-08-19 18:51:06
Following order registers the date and time for a given UNIX timestamp 1092941466 and make up for your neighborhood timezone.
sqlite> SELECT datetime(1092941466, 'unixepoch', 'localtime');
2004-08-19 13:51:06
Following order figures the current UNIX timestamp.
sqlite> SELECT strftime('%s','now');
1393348134
Following order figures the quantity of days since the marking of the US Declaration of Independence.
sqlite> SELECT julianday('now') - julianday('1776-07-04');
86798.7094695023
Following order figures the quantity of seconds since a specific second in 2004.
sqlite> SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');
295001572
Following order figures the date of the principal Tuesday in October for the current year.
sqlite> SELECT date('now','start of year','+9 months','weekday 2');
2013-10-01
Following order figures the time since the UNIX age in a flash (like strftime('%s','now') aside from incorporates fragmentary part).
sqlite> SELECT (julianday('now') - 2440587.5)*86400.0;
1367926077.12598
To change over among UTC and neighborhood time esteems when designing a date, utilize the utc or localtime modifiers as follows −
sqlite> SELECT time('12:00', 'localtime');
05:00:00
sqlite> SELECT time('12:00', 'utc');
19:00:00