SurrealDB Docs Logo

Enter a search query

Formatters

The string::is::datetime and time::format functions in SurrealQL accept certain text formats for date/time formatting. The possible formats are listed below.

Date formatters

SpecifierExampleDescription
%Y2001The full proleptic Gregorian year, zero-padded to 4 digits.
%C20The proleptic Gregorian year divided by 100, zero-padded to 2 digits.
%y01The proleptic Gregorian year modulo 100, zero-padded to 2 digits.
%m07Month number (01 to 12), zero-padded to 2 digits.
%bJulAbbreviated month name. Always 3 letters.
%BJulyFull month name.
%hJulSame as %b.
%d08Day number (01 to 31), zero-padded to 2 digits.
%e8Same as %d but space-padded. Same as %_d.
%aSunAbbreviated weekday name. Always 3 letters.
%ASundayFull weekday name.
%w0Day of the week. Sunday = 0, Monday = 1, …, Saturday = 6.
%u7Day of the week. Monday = 1, Tuesday = 2, …, Sunday = 7. (ISO 8601)
%U28Week number starting with Sunday (00 to 53), zero-padded to 2 digits.
%W27Same as %U, but week 1 starts with the first Monday in that year instead.
%G2001Same as %Y but uses the year number in ISO 8601 week date.
%g01Same as %y but uses the year number in ISO 8601 week date.
%V27Same as %U but uses the week number in ISO 8601 week date (01 to 53).
%j189Day of the year (001 to 366), zero-padded to 3 digits.
%D07/08/01Month-day-year format. Same as %m/%d/%y.
%x07/08/01Locale’s date representation.
%F2001-07-08Year-month-day format (ISO 8601). Same as %Y-%m-%d.
%v8-Jul-2001Day-month-year format. Same as %e-%b-%Y.

Time formatters

SpecifierExampleDescription
%H00Hour number (00 to 23), zero-padded to 2 digits.
%k0Same as %H but space-padded. Same as %_H.
%I12Hour number in 12-hour clocks (01 to 12), zero-padded to 2 digits.
%l12Same as %I but space-padded. Same as %_I.
%Pamam or pm in 12-hour clocks.
%pAMAM or PM in 12-hour clocks.
%M34Minute number (00 to 59), zero-padded to 2 digits.
%S60Second number (00 to 60), zero-padded to 2 digits.
%f026490000The fractional seconds (in nanoseconds) since last whole second.
%.f.026490Similar to %f but left-aligned.
%.3f.026Similar to .%f but left-aligned but fixed to a length of 3.
%.6f.026490Similar to .%f but left-aligned but fixed to a length of 6.
%.9f.026490000Similar to .%f but left-aligned but fixed to a length of 9.
%3f026Similar to %.3f but without the leading dot.
%6f026490Similar to %.6f but without the leading dot.
%9f026490000Similar to %.9f but without the leading dot.
%R00:34Hour-minute format. Same as %H:%M.
%T00:34:59Hour-minute-second format. Same as %H:%M:%S.
%X00:34:59Locale’s time representation.
%r12:34:59 AMHour-minute-second format in 12-hour clocks. Same as %I:%M:%S %p.
%x07/08/01Locale’s date representation.
%F2001-07-08Year-month-day format (ISO 8601). Same as %Y-%m-%d.
%v8-Jul-2001Day-month-year format. Same as %e-%b-%Y.

Timezones formatters

SpecifierExampleDescription
%ZACSTLocal time zone name.
%z+0930Offset from the local time to UTC (with UTC being +0000).
%:z+09:30Same as %z but with a colon.

Date & time formatters

SpecifierExampleDescription
%cSun Jul 8 00:34:59 2001Locale’s date and time.
%+2001-07-08T00:34:59.026490+09:30ISO 8601 / RFC 3339 date & time format.
%s994518299UNIX timestamp, the number of seconds since 1970-01-01T00:00:00.

Other formatters

SpecifierExampleDescription
%t-Literal tab (\t).
%n-Literal newline (\n).
%%-Literal percent sign.

Examples

Seeing if an input with a date and time conforms to an expected format:

RETURN string::is::datetime("5sep2024pm012345.6789", "%d%b%Y%p%I%M%S%.f");
Response
true

Another example with a different format:

RETURN string::is::datetime("23:56:00 2015-09-05", "%Y-%m-%d %H:%M");
Response
false

Using a formatter to generate a string from a datetime:

RETURN time::format("2021-11-01T08:30:17+00:00", "%Y-%m-%d");
Response
"2021-11-01"

On this page

© SurrealDB GitHub Discord Community Cloud Features Releases Install