STRFTIME
STRFTIME is an SQL function used to format datetime values into a string following designated format patterns. It provides flexibility and control over output display of date and time data within an SQL database.
STRFTIME(format, timestring, modifier, modifier, …)
- format: This parameter defines the exact string layout for the result, including placeholders for components of the date/time such as %H for hour or %Y for year.
- timestring: This parameter represents the initial date/time string to process. It can be a standard numerical date/time or refer to now for the current date/time.
- modifier: This optional parameter modifies the output or input. Multiple modifiers can be used. For instance, ‘localtime’ converts the timestring to the local time zone before processing, and ‘utc’ converts it to UTC.
Example
SELECT STRFTIME('%Y-%m-%d %H:%M:%S', 'now');Output
2021-12-08 10:15:25Explanation
In the provided example, STRFTIME function was used to format the current date and time (obtained by now) to the ‘Year-Month-Day Hour:Minute:Second’ format. The %Y represents a 4-digit year, %m is for a 2-digit month, %d for a 2-digit day, %H for a 2-digit hour (24-hour clock), %M is for a 2-digit minute, and %S is for a 2-digit second. Each format specifiers are surrounded by single quotes to denote that it is a single string argument passed to the STRFTIME function.
STRFTIME(pattern, timestamp)
- pattern: This is a character sequence representing the format of the date-time output. Special pattern letters help define the output format. For example, ‘%Y’ will output the year in four digits, and ‘%m’ will output the month as a two-digit number.
- timestamp: This is the input date, time, or timestamp value that you want to format. This could be a column in a table that contains timestamp values, or it can be a timestamp string such as ‘2021-03-31 12
Example
SELECT STRFTIME('%Y-%m-%d %H:%M:%S', 'now');Output
2022-07-10 23:30:00Explanation
The STRFTIME function formats a date/time string using a specific format string. The example is using the ‘now’ keyword to get the current date and time. The format string ‘%Y-%m-%d %H:%M:%S’ instructs STRFTIME to output the date and time in the format ‘YYYY-MM-DD hh:mm:ss’.