{"id":761,"date":"2016-01-03T10:22:01","date_gmt":"2016-01-03T03:22:01","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=761"},"modified":"2022-04-03T15:03:54","modified_gmt":"2022-04-03T08:03:54","slug":"sqlite-date","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-date\/","title":{"rendered":"SQLite Date & Time"},"content":{"rendered":"\r\n

Summary<\/strong>: in this tutorial, we will show you how to work with the SQLite date and time values and use the built-in dates and times functions to handle date and time values.<\/p>\r\n\r\n\r\n\r\n

SQLite does not support built-in date and\/or time storage class.<\/a> Instead, it leverages some built-in date and time functions to use other storage classes<\/a> such as TEXT<\/code>, REAL<\/code>, or INTEGER<\/code> for storing the date and time values.<\/p>\r\n\r\n\r\n\r\n

Using the TEXT storage class for storing SQLite date and time<\/h2>\r\n\r\n\r\n\r\n

If you use the TEXT<\/code> storage class to store date and time value, you need to use the ISO8601 string format as follows:<\/p>\r\n\r\n\r\n

YYYY-MM-DD HH:MM:SS.SSS<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
For example, 2016-01-01 10:20:05.123<\/code><\/p>\r\n\r\n\r\n\r\n
CREATE<\/span> TABLE<\/span> datetime_text(\r\n   d1 text<\/span>, \r\n   d2 text<\/span>\r\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
The table contains two column d1<\/code> and d2<\/code> with TEXT<\/code> datatype.<\/p>\r\n\r\n\r\n\r\n
To insert date and time values into the datetime_text<\/code> table, you use the DATETIME<\/a><\/code> function.<\/p>\r\n\r\n\r\n\r\n
For example, to get the current UTC date and time value, you pass the now literal string to the function as follows:<\/p>\r\n\r\n\r\n
SELECT<\/span> datetime('now'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
To get the local time, you pass an additional argument  localtime<\/code>.<\/p>\r\n\r\n\r\n
SELECT<\/span> datetime('now'<\/span>,'localtime'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Second, insert<\/a> the date and time values into the datetime_text<\/code> table as follows:<\/p>\r\n\r\n\r\n
INSERT<\/span> INTO<\/span> datetime_text (d1, d2)\r\nVALUES<\/span>(datetime('now'<\/span>),datetime('now'<\/span>, 'localtime'<\/span>));<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Third, query the data<\/a> from the datetime_text<\/code> table.<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n\td1,\r\n\ttypeof(d1),\r\n\td2,\r\n\ttypeof(d2)\r\nFROM<\/span>\r\n\tdatetime_text;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
\"SQLite<\/figure>\r\n\r\n\r\n\r\n
Using REAL storage class to store SQLite date and time values<\/h2>\r\n\r\n\r\n\r\n
You can use the REAL<\/code> storage class to store the date and\/ or time values as Julian day numbers<\/a>, which is the number of days since noon in Greenwich on November 24, 4714 B.C. based on the proleptic Gregorian calendar.<\/p>\r\n\r\n\r\n\r\n
Let’s take a look at an example of using the REAL storage class to store date and time values.<\/p>\r\n\r\n\r\n\r\n
First, create a new table<\/a> named datetime_real<\/code>.<\/p>\r\n\r\n\r\n
CREATE<\/span> TABLE<\/span> datetime_real(\r\n   d1 real<\/span>\r\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Second, insert the “current” date and time value into the datetime_real<\/code> table.<\/p>\r\n\r\n\r\n
INSERT<\/span> INTO<\/span> datetime_real (d1)\r\nVALUES<\/span>(julianday('now'<\/span>));<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
We used the  julianday()<\/code> function to convert the current date and time to the Julian Day.<\/p>\r\n\r\n\r\n\r\n
Third, query data from the datetime_real<\/code> table.<\/p>\r\n\r\n\r\n
SELECT<\/span> d1 FROM<\/span> datetime_real;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
\"SQLite<\/figure>\r\n\r\n\r\n\r\n
The output is not human readable.<\/p>\r\n\r\n\r\n\r\n
Fortunately, you can use the built-in date()<\/a><\/code> and time()<\/a><\/code> functions to format a date and time value as follows:<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n\tdate<\/span>(d1),\r\n\ttime<\/span>(d1)\r\nFROM<\/span>\r\n\tdatetime_real;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
\"SQLite<\/figure>\r\n\r\n\r\n\r\n
Using INTEGER to store SQLite date and time values<\/h2>\r\n\r\n\r\n\r\n
Besides  TEXT<\/code> and REAL<\/code> storage classes, you can use the INTEGER<\/code> storage class to store date and time values.<\/p>\r\n\r\n\r\n\r\n
We typically use the INTEGER<\/code> to store UNIX time which is the number of seconds since 1970-01-01 00:00:00 UTC<\/code>. See the following example:<\/p>\r\n\r\n\r\n\r\n
First, create a table<\/a> that has one column whose data type is INTEGER<\/code> to store the date and time values.<\/p>\r\n\r\n\r\n
CREATE<\/span> TABLE<\/span> datetime_int (d1 int<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Second, insert<\/a> the current date and time value into the datetime_int<\/code> table.<\/p>\r\n\r\n\r\n
INSERT<\/span> INTO<\/span> datetime_int (d1)\r\nVALUES<\/span>(strftime('%s'<\/span>,'now'<\/span>));<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Third, query<\/a> data from the datetime_int<\/code> table.<\/p>\r\n\r\n\r\n
SELECT<\/span> d1 FROM<\/span> datetime_int;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
It’s an integer.<\/p>\r\n\r\n\r\n\r\n
To format the result, you can use the built-in datetime()<\/a><\/code> function as follows:<\/p>\r\n\r\n\r\n
SELECT<\/span> datetime(d1,'unixepoch'<\/span>)\r\nFROM<\/span> datetime_int;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
\"SQlite<\/figure>\r\n\r\n\r\n\r\n
Using SQLite, you can freely choose any data types to store date and time values and use the built-in dates and times function to convert between formats.<\/p>\r\n\r\n\r\n\r\n
For the detailed information on SQLite dates and times functions, check it out the built-in dates and times functions<\/a>.<\/p>\r\n\r\n\r\n\r\n
In this tutorial, you have learned how to use the TEXT<\/code>, REAL<\/code>, and INTEGER<\/code> storage classes to store date and time values. In addition, you learned how to use the built-in dates and times functions to convert the stored date and times values into readable formats.<\/p>\r\n
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
Try It<\/a><\/p>\r\n\r\n\r\n\r\n
Try It<\/a><\/p>\r\n\r\n\r\n\r\n
Try It<\/a><\/p>\r\n\r\n\r\n\r\n
Try It<\/a><\/p>\r\n\r\n\r\n\r\n
Try It<\/a><\/p>\r\n\r\n\r\n\r\n
Try It<\/a><\/p>\r\n\r\n\r\n\r\n
Try It<\/a><\/p>\r\n\r\n\r\n\r\n
Try It<\/a><\/p>\r\n\r\n\r\n\r\n
Try It<\/a><\/p>\r\n\r\n\r\n\r\n
Try It<\/a><\/p>\r\n\r\n\r\n\r\n
Try It<\/a><\/p>\r\n\r\n\r\n\r\n
Try It<\/a><\/p>\r\n\r\n\r\n\r\n
Try It<\/a><\/p>\r\n\r\n\r\n\r\n
First, create a new table<\/a> named datetime_text<\/code> for demonstration.<\/p>\r\n\r\n\r\n