SQLite Date and Time functions

SQLite database supports a wide range of date and time functions. These functions allow users to work with dates and times in various formats, manipulate them, and extract useful information. Date functions can be used to perform a range of operations on dates and times, such as formatting them in different ways, calculating the difference between two dates or times, and extracting specific components of a date or time.

SQLite Date functions

Here are some of the most commonly used date and time functions in SQLite:

date()

The date() function returns the current date in the format ‘YYYY-MM-DD’. For example, the following SQL query will return the current date:

SELECT date('now');

time()

The time() function returns the current time in the format ‘HH:MM:SS’. For example, the following SQL query will return the current time:

SELECT time('now');

datetime()

The datetime() function returns the current date and time in the format ‘YYYY-MM-DD HH:MM:SS’. For example, the following SQL query will return the current date and time:

SELECT datetime('now');

strftime()

The strftime() function is used to format dates and times. It takes two arguments: the format string and the date or time value to be formatted. The format string specifies the desired output format. For example, the following SQL query will return the current year:

SELECT strftime('%Y', 'now');

Here are some commonly used format specifiers for the strftime() function:

%Y – year (four digits)
%m – month (01-12)
%d – day of the month (01-31)
%H – hour (00-23)
%M – minute (00-59)
%S – second (00-59)

julianday()

The julianday() function is used to convert a date or time value to a Julian day number. A Julian day number is a continuous count of days since January 1, 4713 BC. For example, the following SQL query will return the Julian day number for the current date:

SELECT julianday('now');

strftime(‘%s’, datetime)

The strftime('%s', datetime) function returns the Unix timestamp for a given datetime value. The Unix timestamp is a count of seconds since January 1, 1970, 00:00:00 UTC. For example, the following SQL query will return the Unix timestamp for the current date and time:

SELECT strftime('%s', 'now');

In conclusion, SQLite offers a rich set of date and time functions that can be used to manipulate, format, and extract information from dates and times. These functions are useful for a variety of applications, including data analysis, record-keeping, and scheduling.