Temporal functions

Warning

Quine relies on Java libraries when processing temporal functions. In most cases inconsistent or invalid parameters passed to Quine's temporal functions will result in errors, however there are some cases where Quine can return an unexpected result.

For example, dayOfQuarter = 93 returns java.time.DateTimeException: Invalid value for DayOfQuarter (valid values 1 - 90/92): 93 but dayOfQuarter=92 instead overflows and gives back April 1 (first day of the next quarter) if the quarter does not have 92 days.

Offsets vs Timezones

An offset refers to a number of minutes (Quine supports precision down to 15 minutes) ahead of or behind UTC. A timezone string like “US/Pacific” may be used to refer to an offset, but only the offset is stored, not the alias. Valid timezone strings are those recognized by the JVM as zone IDs, and may vary depending on which JVM is used to run Quine.

DateTime¶

A DateTime represents an absolute moment at a specific offset from UTC. For example, May 21, 2002, at 2:30:00PM UTC-8, or July 30, 2039, at 12:00:00AM UTC. DateTimes with different offsets may refer to the same moment in time and are still distinct. For example, June 11, 4 PM UTC-8 is not the same DateTime as June 11, 7 PM UTC-5, even though they refer to the same moment in time (e.g., the same unix timestamp).

datetime() - Returns the server’s current clock time in the server’s timezone.

RETURN datetime()

{
    "columns": [
        "datetime()"
    ],
    "results": [
        [
            "2023-05-04T15:26:07.769696-05:00[America/Chicago]"
        ]
    ]
}

datetime(datetime: string) - Expects an ISO-8601 extended offset date-time string. For example, 2011-12-03T10:15:30+01:00. The string may also include an optional timezone in square brackets, but the DateTime value created will track only the UTC offset, not the timezone name.

RETURN datetime("2011-12-03T10:15:30+01:00")

{
    "columns": [
        "datetime(\"2011-12-03T10:15:30+01:00\")"
    ],
    "results": [
        [
            "2011-12-03T10:15:30+01:00"
        ]
    ]
}

datetime(datetime: string, format: string) - Expects a DateTime string parseable according to the format string. The format string is interpreted using Java’s syntax for datetime formats. Documentation available at https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html#patterns

RETURN datetime("Wed, 1 May 2019 11:05:30 EST", "E, d MMM yyyy HH:mm:ss z")

{
    "columns": [
        "datetime(\"Wed, 1 May 2019 11:05:30 EST\", \"E, d MMM yyyy HH:mm:ss z\")"
    ],
    "results": [
        [
            "2019-05-01T11:05:30-04:00[America/New_York]"
        ]
    ]
}

datetime(options: map) - Options may be any combination of the following. If empty, the map is ignored, and the behavior is the same as datetime()
date: a localdatetime or datetime to use as a base date
- When given a localdatetime: interpret the localdatetime as a datetime in the provided or default timezone (see below)
- When given a datetime: interpret the datetime as a datetime in its original timezone, if no timezone is provided. If a timezone is provided, convert the provided datetime to the equivalent moment in the provided timezone
- eg datetime({date: datetime("2011-12-03T10:15:30+01:00"), timezone: "EST"}) = datetime("2011-12-03T04:15:30-05:00")
- When neither is provided, Jan 1 0000 is used
timezone: a string representing a timezone whose offset should be used (ex: PST or America/Los_Angeles). If the timezone can’t be parsed to an offset, the offset 0 (UTC) will be used. If no timezone is provided and no date is provided, the server timezone will be used. The zone provided must be a valid Java ZoneId (which may vary depending on the JVM)
year: integer representing the years since the base date
quarter: integer (1-4) representing the quarter within the year
month: integer (1-12) representing the month within the year
week: integer (1-53) representing the week within the (week-based) year
dayOfQuarter: integer (1-92) representing the day within the quarter
day: integer (1-31) representing the day within the month
ordinalDay: integer (1-366) representing the day within the year
dayOfWeek: integer (1-7) representing the day within the week
hour: integer (0-23) representing the hour within the day
minute: integer (0-59) representing the minute within the hour
second: integer (0-59) representing the second within the minute
millisecond: integer (0-999) representing the millisecond within the second
microsecond: integer (0-999,999) representing the microsecond within the second
nanosecond: integer (0-999,999,999) representing the nanosecond within the second
epochMillis: integer representing the number of milliseconds since the epoch
epochSeconds: integer representing the number of seconds since the epoch

WITH datetime({ year: 1984, month: 11, day: 11, hour: 12, minute: 31, second: 14, nanosecond: 645876123, timezone: "Europe/Stockholm" }) AS d RETURN d.year, d.quarter, d.month, d.week, d.weekYear, d.day, d.ordinalDay, d.dayOfWeek, d.dayOfQuarter

{
    "columns": [
        "d.year",
        "d.quarter",
        "d.month",
        "d.week",
        "d.weekYear",
        "d.day",
        "d.ordinalDay",
        "d.dayOfWeek",
        "d.dayOfQuarter"
    ],
    "results": [
        [
            1984,
            4,
            11,
            45,
            null,
            11,
            316,
            7,
            42
        ]
    ]
}

LocalDateTime¶

A LocalDateTime represents a date and time, but no specific offset from UTC. For example, August 2nd, 2081 at 3:00:00PM. Note that as they lack a UTC offset, LocalDateTimes do not represent a specific moment, so questions like “how many seconds was August 2nd, 2018 at 3PM after the epoch?” are ill-defined.

localdatetime() - Returns the server’s current clock time in the server’s timezone

RETURN localdatetime()

{
    "columns": [
        "localdatetime()"
    ],
    "results": [
        [
            "2023-05-05T09:21:30.872348"
        ]
    ]
}

localdatetime(datetime: string) - Expects an ISO-8601 date-time string. For example, 2021-01-03T23:11:04.

RETURN localdatetime("2021-01-03T23:11:04")

{
    "columns": [
        "localdatetime(\"2021-01-03T23:11:04\")"
    ],
    "results": [
        [
            "2021-01-03T23:11:04"
        ]
    ]
}

localdatetime(datetime: string, format: string) - Expects a datetime string parseable according to the format string. The format string is interpreted using java’s syntax for datetime formats. Documentation available at https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html#patterns

RETURN localdatetime("Wed, 1 May 2019 11:05:30 EST", "E, d MMM yyyy HH:mm:ss z")

{
  "columns": [
    "localdatetime(\"Wed, 1 May 2019 11:05:30 EST\", \"E, d MMM yyyy HH:mm:ss z\")"
  ],
  "results": [
    [
      "2019-05-01T11:05:30"
    ]
  ]
}

localdatetime(options: map). Options may be any combination of the following. If empty, the behavior is the same as localdatetime(). Alternatively, a timezone may be provided as the only field, in which case the server’s clock time in the provided timezone will be returned (or UTC if the timezone could not be parsed).
date: a localdatetime or datetime to use as a base datetime
- When given a datetime or localdatetime: interpret the datetime as a localdatetime by discarding any timezone component
- When neither is provided, Jan 1 0000 is used
year: integer representing the years since the base date
quarter: integer (1-4) representing the quarter within the year
month: integer (1-12) representing the month within the year
week: integer (1-53) representing the week within the (week-based) year
dayOfQuarter: integer (1-92) representing the day within the quarter
day: integer (1-31) representing the day within the month
ordinalDay: integer (1-366) representing the day within the year
dayOfWeek: integer (1-7) representing the day within the week
hour: integer (0-23) representing the hour within the day
minute: integer (0-59) representing the minute within the hour
second: integer (0-59) representing the second within the minute
millisecond: integer (0-999) representing the millisecond within the second
microsecond: integer (0-999,999) representing the microsecond within the second
nanosecond: integer (0-999,999,999) representing the nanosecond within the second
epochMillis: integer representing the number of milliseconds since the epoch
epochSeconds: integer representing the number of seconds since the epoch

RETURN localdatetime({ year: 1995, month: 4, day: 25, hour: 5, minute: 1, second: 53 })

{
  "columns": [
    "localdatetime({ year: 1995, month: 4, day: 25, hour: 5, minute: 1, second: 53 })"
  ],
  "results": [
    [
      "1995-04-25T05:01:53"
    ]
  ]
}

Date¶

Date represents an unzoned date. Examples of dates are February 2, 2009; November 28, 1941. Dates do not contain any offset information.

date() - Returns the server’s current date

RETURN date()

{
  "columns": [
    "date()"
  ],
  "results": [
    [
      "2023-05-05T09:36:25.941144"
    ]
  ]
}

date(date: string) - Expects an ISO-8601 date string. For example, '1986-06-07'

RETURN date("1986-06-07")

{
  "columns": [
    "date(\"1986-06-07\")"
  ],
  "results": [
    [
      "1986-06-07"
    ]
  ]
}

date(date: string, format: string) - Expects a date string parseable according to the format string. The format string is interpreted using java’s syntax for datetime formats. Documentation available at https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html#patterns

RETURN date("Wed, Jul 4, '01", "EEE, MMM d, ''yy")

{
  "columns": [
    "date(\"Wed, Jul 4, '01\", \"EEE, MMM d, ''yy\")"
  ],
  "results": [
    [
      "2001-07-04"
    ]
  ]
}

date(options: map) - Options may be any combination of the following. All options modify a base date of Jan 1 0000
year: integer representing the years since the base date
quarter: integer (1-4) representing the quarter within the year
month: integer (1-12) representing the month within the year
week: integer (1-53) representing the week within the (week-based) year
dayOfQuarter: integer (1-92) representing the day within the quarter
day: integer (1-31) representing the day within the month
ordinalDay: integer (1-366) representing the day within the year
dayOfWeek: integer (1-7) representing the day within the week

RETURN date({ year: 1995, month: 4, day: 24 })

{
  "columns": [
    "date({ year: 1995, month: 4, day: 24 })"
  ],
  "results": [
    [
      "1995-04-24"
    ]
  ]
}

Time¶

Time represents a time in at a specific offset from UTC. Examples of times are “1:27 AM UTC+1”, “4:05 PM UTC”, “11:22 PM UTC+9”. As with DateTime, multiple times may represent the same time within a day, but if they have different offsets, the time values are still considered distinct.

time() - returns the server’s current clock time in the server’s timezone

RETURN time()

{
  "columns": [
    "time()"
  ],
  "results": [
    [
      "09:47:05.660853"
    ]
  ]
}

time(time: string) - Expects an ISO-8601 extended time-string. For example, 19:45:03. The offset portion will be truncated to a 15-minute interval.

RETURN time("19:45:03")

{
  "columns": [
    "time(\"19:45:03\")"
  ],
  "results": [
    [
      "19:45:03"
    ]
  ]
}

time(time: string, format: string) - Expects a time string parseable according to the format string. The format string is interpreted using java’s syntax for time formats. Documentation available at https://docs.oracle.com/javase/8/docs/api/java/time/package-summary.html

RETURN time("Apr 1, 11 oclock in '19", "MMM d, HH 'oclock in '''yy")

{
  "columns": [
    "time(\"Apr 1, 11 oclock in '19\", \"MMM d, HH 'oclock in '''yy\")"
  ],
  "results": [
    [
      "11:00"
    ]
  ]
}

time(options: map) Options may be any combination of:
hour: integer (0-23) representing the hour within the day
minute: integer (0-59) representing the minute within the hour
second: integer (0-59) representing the second within the minute
millisecond: integer (0-999) representing the millisecond within the second
microsecond: integer (0-999,999) representing the microsecond within the second
nanosecond: integer (0-999,999,999) representing the nanosecond within the second
offsetSeconds: integer representing the number of seconds (-64800 to 64800 in 15-minute/900-second increments) offset from UTC

RETURN time({ hour: 10, minute: 4, second: 24, nanosecond: 110 })

{
  "columns": [
    "time({ hour: 10, minute: 4, second: 24, nanosecond: 110 })"
  ],
  "results": [
    [
      "10:04:24.000000110"
    ]
  ]
}

LocalTime¶

LocalTime represents a time at no specific offset from UTC. Examples include “3:00 PM”, “6:40 AM”, and “5:05 PM”. LocalTimes contain no offset information

localtime() - returns the server’s current clock time
localtime(time: string) - Expects an ISO-8601 extended time-string. For example, 12:45:03.
localtime(time: string, format: string) - Expects a time string parseable according to the format string. The format string is interpreted using java’s syntax for time formats. Documentation available at https://docs.oracle.com/javase/8/docs/api/java/time/package-summary.html
localtime(options: map) Options may be any combination of:
hour: integer (0-23) representing the hour within the day
minute: integer (0-59) representing the minute within the hour
second: integer (0-59) representing the second within the minute
millisecond: integer (0-999) representing the millisecond within the second
microsecond: integer (0-999,999) representing the microsecond within the second
nanosecond: integer (0-999,999,999) representing the nanosecond within the second

Duration¶

Duration represents a difference between 2 temporal values of the same type (either datetime or localdatetime) in the ISO-8601 duration format. For example, the duration between May 3 2020 and May 8 2020 is 120 hours. The duration between February 28 2000 and March 1 2000 is 48 hours, while the duration between February 28 2001 and March 1 2001 is 24 hours. The duration between 1:30 PM UTC-1 (from a datetime) and 2:00 PM (at no offset, from a localdatetime) is undefined. As a final example, the duration between May 29 1986, 2PM PST and May 30, 1986 12AM UTC is 3 hours.

RETURN duration({ days: 24 })

{
  "columns": [
    "duration({ days: 24 })"
  ],
  "results": [
    [
      "PT576H"
    ]
  ]
}

RETURN duration.between(localdatetime({day: 3, month: 5, year:2020}), localdatetime({day: 8, month: 5, year:2020}))

{
  "columns": [
    "duration.between(localdatetime({day: 3, month: 5, year:2020}), localdatetime({day: 8, month: 5, year:2020}))"
  ],
  "results": [
    [
      "PT120H"
    ]
  ]
}

RETURN duration.between(datetime({day: 28, month: 2, year:2000}), datetime({day: 1, month: 3, year:2000}))

{
  "columns": [
    "duration.between(datetime({day: 28, month: 2, year:2000}), datetime({day: 1, month: 3, year:2000}))"
  ],
  "results": [
    [
      "PT48H"
    ]
  ]
}

RETURN duration.between(datetime({day: 28, month: 2, year:2001}), datetime({day: 1, month: 3, year:2001}))

{
  "columns": [
    "duration.between(datetime({day: 28, month: 2, year:2001}), datetime({day: 1, month: 3, year:2001}))"
  ],
  "results": [
    [
      "PT24H"
    ]
  ]
}

RETURN duration.between(datetime({day: 29, month: 5, hour: 14, year: 1986, timezone: "PST"}), datetime({day: 30, month: 5, hour: 0, year: 1986, timezone: "UTC"}))

{
  "columns": [
    "duration.between(datetime({day: 29, month: 5, hour: 14, year: 1986, timezone: \"PST\"}), datetime({day: 30, month: 5, hour: 0, year: 1986, timezone: \"UTC\"}))"
  ],
  "results": [
    [
      "PT3H"
    ]
  ]
}