In Softr Databases, formula fields, workflows and in a few other select places within Softr, you may need to reformat date or datetimes in specific ways. The glossary below is a guide for creating these timestamps in various formats for user-friendly and computer-friendly dates and datetimes.

Date-Time Pattern Reference

All letters ‘A’ to ‘Z’ and ‘a’ to ‘z’ are reserved as pattern letters. The following pattern letters are defined:

Pattern Letters

Pattern Letter Count

The count of pattern letters determines the format:

Text

The text style is determined based on the number of pattern letters used:

• Less than 4 letters: Short form
• Exactly 4 letters: Full form
• Exactly 5 letters: Narrow form

Pattern letters ‘L’, ‘c’, and ‘q’ specify the stand-alone form of the text styles.

Number

If the count of letters is one, then the value is output using the minimum number of digits and without padding. Otherwise, the count of digits is used as the width of the output field, with the value zero-padded as necessary.

Constraints:

• Only one letter of ‘c’ and ‘F’ can be specified
• Up to two letters of ‘d’, ‘H’, ‘h’, ‘K’, ‘k’, ‘m’, and ‘s’ can be specified
• Up to three letters of ‘D’ can be specified

Number/Text

• 3 or more letters: Use the Text rules above
• Otherwise: Use the Number rules above

Fraction

Outputs the nano-of-second field as a fraction-of-second. The nano-of-second value has nine digits, thus the count of pattern letters is from 1 to 9. If it is less than 9, then the nano-of-second value is truncated, with only the most significant digits being output.

Year

The count of letters determines the minimum field width below which padding is used. If the count of letters is two, then a reduced two digit form is used:

• For printing: Outputs the rightmost two digits
• For parsing: Parses using the base value of 2000, resulting in a year within the range 2000 to 2099 inclusive

If the count of letters is less than four (but not two), then the sign is only output for negative years as per SignStyle.NORMAL. Otherwise, the sign is output if the pad width is exceeded, as per SignStyle.EXCEEDS_PAD.

Special Formatting Rules

Zone ID (V)

Outputs the time-zone ID, such as ‘Europe/Paris’. If the count of letters is two, then the time-zone ID is output. Any other count of letters throws IllegalArgumentException.

Zone Names (z)

Outputs the display name of the time-zone ID:

• 1-3 letters: Short name
• 4 letters: Full name
• 5+ letters: Throws IllegalArgumentException

Offset X and x

Formats the offset based on the number of pattern letters:

• One letter: Just the hour (e.g., ‘+01’), unless the minute is non-zero (e.g., ‘+0130’)
• Two letters: Hour and minute without colon (e.g., ‘+0130’)
• Three letters: Hour and minute with colon (e.g., ‘+01:30’)
• Four letters: Hour, minute, and optional second without colon (e.g., ‘+013015’)
• Five letters: Hour, minute, and optional second with colon (e.g., ‘+01:30:15’)
• 6+ letters: Throws IllegalArgumentException

Note: Pattern letter ‘X’ (upper case) outputs ‘Z’ when the offset is zero, whereas ‘x’ (lower case) outputs ‘+00’, ‘+0000’, or ‘+00:00’.

Offset O

Formats the localized offset based on the number of pattern letters:

• One letter: Short form of localized offset text (e.g., ‘GMT+8’)
• Four letters: Full form with 2-digit hour and minute (e.g., ‘GMT+08:00’)
• Other counts: Throws IllegalArgumentException

Offset Z

Formats the offset based on the number of pattern letters:

• 1-3 letters: Hour and minute without colon (e.g., ‘+0130’). Outputs ‘+0000’ when offset is zero
• Four letters: Full form of localized offset (equivalent to four letters of Offset-O). Outputs corresponding localized offset text if offset is zero
• Five letters: Hour, minute, with optional second if non-zero, with colon. Outputs ‘Z’ if offset is zero
• 6+ letters: Throws IllegalArgumentException

Optional Section

The optional section markers [ and ] work exactly like calling DateTimeFormatterBuilder.optionalStart() and DateTimeFormatterBuilder.optionalEnd().

Pad Modifier (p)

Modifies the pattern that immediately follows to be padded with spaces. The pad width is determined by the number of pattern letters. This is the same as calling DateTimeFormatterBuilder.padNext(int).

Example: ‘ppH’ outputs the hour-of-day padded on the left with spaces to a width of 2.

Important Notes

• Any unrecognized letter is an error
• Any non-letter character, other than [, ], {, }, # and the single quote will be output directly
• Despite this, it is recommended to use single quotes around all characters that you want to output directly to ensure that future changes do not break your application