sqlite
  1. sqlite-datetime
PreviousNext

SQLite Date & Time

SQLite has built-in support for date and time data types, offering a wide array of functions to manipulate and retrieve data based on time. The datetime() function in SQLite is an extremely powerful tool to handle date and time data types.

Syntax

The syntax for the datetime() function in SQLite is as follows:

datetime(datetime_expression, modifier, modifier, ...);

The first parameter represents the expression of the datetime (either a date or time) or a numeric value that represents the Julian Day. The second and subsequent optional parameters represent any modifiers that can be used to manipulate the expression.

Example

Let's say we have a table named employees containing information about different employees. We can use datetime() function to create a new column called hired_date to store date and time the employees were hired.

ALTER TABLE employees ADD COLUMN hired_date TEXT;
UPDATE employees SET hired_date = datetime('now');

Output

We can retrieve the data from the employees table using a SELECT statement, as follows:

SELECT id, name, hired_date FROM employees;

The output of the SELECT statement would be:

 id | name  |           hired_date
----+-------+----------------------------
  1 | John  | 2022-03-12 04:27:05.757444
  2 | Jane  | 2022-03-12 04:27:05.757444
  3 | Alice | 2022-03-12 04:27:05.757444
  4 | Bob   | 2022-03-12 04:27:05.757444

Explanation

In the example above, we create a new column hired_date in employees table and update the column to insert the current date and time by using SQLite datetime() function.

We retrieve the data from the employees table using the SELECT statement which returns id, name, and hired_date columns from the table.

Use

SQLite date and time functions are used for formatting datetime, performing conversions between timestamps, and extracting date and time parts from datetime. These functions are useful for handling and manipulating date and time values in various databases.

Date and time operations are commonly used to:

1. Record events
2. Track inventory
3. Schedule appointments
4. Calculate time differences

Important Points

  • The datetime() function is used to format and input date and time information.
  • SQLite supports a variety of date and time functions, including DATE(), TIME(), and STRFTIME().
  • SQLite supports the TIMESTAMP data type which allows a date and time value to be stored as a single numeric value.
  • It is important to ensure that data is in the correct format and consistent when storing and querying time values.
  • SQLite datetime() function returns the value in UTC timezone.

Summary

In this tutorial, we learned about the datetime() function in SQLite and how to use it to manipulate, format, and retrieve date and time data in SQL queries. We also saw an example of using datetime() function to add a new column in a table and update the data for that column. It's important to be aware of different date and time functions supported by SQLite and their usage in various databases.

date()juliandday()
Published on: