The following table shows you the SQLite datetime modifiers that you can use to manipulate date and time values within the datetime functions:
| Modifier | Explanation |
|---|---|
| ±NNN days | Adds or subtracts the specified number of days to/from a date or datetime value. |
| ±NNN hours | Adds or subtracts the specified number of hours to/from a datetime value. |
| ±NNN minutes | Adds or subtracts the specified number of minutes to/from a datetime value. |
| ±NNN seconds | Adds or subtracts the specified number of seconds to/from a datetime value. |
| ±NNN months | Adds or subtracts the specified number of months to/from a date or datetime value. |
| ±NNN years | Adds or subtracts the specified number of years to/from a date or datetime value. |
| ±HH:MM | Adds or subtracts the specified offset in hours and minutes to/from a datetime value. |
| ±HH:MM:SS | Adds or subtracts the specified offset in hours, minutes, and seconds to/from a datetime value. |
| ±HH:MM:SS.SSS | Adds or subtracts the specified offset in hours, minutes, seconds, and milliseconds to/from a datetime value. |
| ±YYYY-MM-DD | Adds or subtracts the specified date to/from a date or datetime value. |
| ±YYYY-MM-DD HH:MM | Adds or subtracts the specified date and time to/from a datetime value. |
| ±YYYY-MM-DD HH:MM:SS | Adds or subtracts the specified date and time to/from a datetime value. |
| ±YYYY-MM-DD HH:MM:SS.SSS | Adds or subtracts the specified date and time to/from a datetime value. |
| start of month | Returns the first day of the month for a given date or datetime value. |
| start of year | Returns the first day of the year for a given date or datetime value. |
| start of day | Returns the start of the day (midnight) for a given date or datetime value. |
| weekday N | Returns the date of the first weekday (Sunday as 0, Monday as 1, etc.) that occurs on or after the specified date or datetime value. |
| unixepoch | Returns the date and time as the number of seconds since 1970-01-01 00:00:00 UTC. |
| julianday | Returns the Julian day number of a given date or datetime value. |
| auto | Automatically detects the format of a date or datetime string and converts it to a datetime value. |
| localtime | Converts a datetime value from UTC to the local timezone. |
| utc | Converts a datetime value from the local timezone to UTC. |
| subsec | Returns the subsecond part of a datetime value. |
| subsecond | Returns the subsecond part of a datetime value as a fractional second. |
Was this tutorial helpful ?
