Class McpDates

java.lang.Object
com.mcpdbwizard.pub.McpDates
public final class McpDates extends Object
How a DATE or TIMESTAMP crosses the Model Context Protocol, in one place.

The generated MCP server used to carry this itself, as new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss") inlined in two places and a comment asking whoever edited one to remember the other. That arrangement had five separate defects and only the first of them was visible:

  1. 1990-01-01 was REFUSED, though the tool schema said "ISO-8601 string" and that is ISO-8601. It is also the form a model reaches for when a column is called HIRE_DATE, so it was the one people hit first.
  2. A trailing zone was SILENTLY DROPPED. SimpleDateFormat.parse(String) stops at the end of its pattern and ignores whatever follows, so ...T22:44:00+05:30 and ...T22:44:00Z both parsed as local time — no error, wrong instant, no signal.
  3. Fractional seconds went the same way, which costs a DATE nothing and costs a TIMESTAMP real data.
  4. Parsing was LENIENT, so 2003-13-45 came back as 2004-02-14 rather than as an error: confident nonsense.
  5. Nothing carried a zone at all, so an accepted string meant different instants on different servers, and — because daylight saving moves — on different dates on the same server.

Items 2 to 4 were the dangerous ones. Item 1 failed closed; those three produced a plausible wrong answer and told nobody.

What is accepted now

A date, optionally a time, optionally a zone offset: 

   1990-01-01                    midnight, server zone
   1990-01-01T09:30              seconds optional
   1990-01-01T09:30:00
   1990-01-01T09:30:00.500       fractional seconds kept
   1990-01-01T09:30:00Z          honoured, not ignored
   1990-01-01T09:30:00+05:30     honoured, not ignored

Anything else is REFUSED with a message naming the accepted forms rather than merely echoing the input. That matters more than politeness: an agent given "Unparseable date" retries blind, and a retry loop against a failing tool churns pooled connections.

An offset is converted, not recorded. An Oracle DATE has no zone to store one in, so 09:30Z becomes whatever 09:30 UTC is on the server's clock. That is the correct reading of the input and it is a real change from the old behaviour, which kept the wall clock and threw the offset away.

Where there is no offset the server's own zone is used. That is not ideal — it is what an Oracle DATE can represent, and inventing UTC instead would silently shift every value that works today.

This lives in pub rather than in emitted source for two reasons: it can be unit-tested here and cannot be tested there, and the generated Jackson configuration can reference ISO_PATTERN instead of repeating the literal — a library constant is not the illegal forward reference that stopped the emitted constant being shared. Copyright 2003-2026 ATB Consultancy Services Ltd (formerly Orinda Software Ltd, Dublin, Ireland)

Since:
2.0.0
Author:
[email protected]

  • Field Details

    • ISO_PATTERN

      public static final String ISO_PATTERN
      How a DATE is rendered on the way out, and what Jackson is configured with.

      Deliberately unchanged from what generated servers have always emitted: altering it would change every date field every existing client reads. Fractional seconds are added on the way out only for a Timestamp, which is the type that actually carries them — see formatTimestamp(java.sql.Timestamp).

      See Also:

    • ISO_PATTERN_MILLIS

      public static final String ISO_PATTERN_MILLIS
      With fractional seconds, for the type that has them.
      See Also:

  • Method Details

    • format

      public static String format(Date theDate)
      Render a date the way every generated server always has.
      Parameters:
      theDate - the value, or null
      Returns:
      the ISO-8601 text, or null for a null input

    • formatTimestamp

      public static String formatTimestamp(Timestamp theTimestamp)
      Render a timestamp, keeping the fractional seconds a DATE does not have.
      Parameters:
      theTimestamp - the value, or null
      Returns:
      the ISO-8601 text with milliseconds, or null for a null input

    • formatAny

      public static String formatAny(Date theDate)
      Render whichever of the two this actually is.

      The generated code reaches every date value through instanceof java.util.Date, and Timestamp extends java.util.Date — so without this dispatch a timestamp renders through the DATE pattern and drops the fractional seconds that are the only reason the type differs. Sending it here rather than widening ISO_PATTERN keeps a plain DATE rendering byte-for-byte as it always has.

      Parameters:
      theDate - the value, or null
      Returns:
      the ISO-8601 text, with milliseconds only for a Timestamp

    • toOracleTimestampText

      public static String toOracleTimestampText(String theIsoText)
      ISO text to the form an Oracle format mask matches, for a zoned timestamp crossing MCP.

      Why this exists rather than a reformat. MCP speaks ISO-8601, where a T separates the date from the time. The generated PL/SQL converts a zoned index-by element with 'yyyy-mm-dd hh24:mi:ss.ff9 TZR', which has a SPACE there — measured, an ISO T against that mask raises ORA-01858. The separator before the ZONE is flexible (with or without a space, offset or region name, all accepted), so this is the only difference that matters.

      It is a TEXTUAL swap, deliberately, and not a parse-and-reformat. Parsing would yield an instant and lose the caller's zone, so rendering it again would substitute the server's — silently turning +05:30 into whatever the server runs on, which is the exact defect this whole area was fixed for.

      Parameters:
      theIsoText - the caller's value, or null
      Returns:
      the same value with the date/time separator Oracle's mask expects

    • fromOracleTimestampText

      public static String fromOracleTimestampText(String theOracleText)
      The reverse: what Oracle handed back, as the ISO text MCP crosses.
      Parameters:
      theOracleText - the value from the database, or null
      Returns:
      the same value with an ISO T separator

    • toOracleDateText

      public static String toOracleDateText(String theIsoText)
      ISO text to the form the Oracle DATE mask matches, for a DATE index-by element crossing MCP.

      Two differences from toOracleTimestampText(java.lang.String), both MEASURED against 12c rather than reasoned about — which is the rule this area exists under, every wrong claim in its history having come from reasoning about a format that one TO_DATE call would have settled:

      • the T becomes a space, exactly as it does for a zoned timestamp; and
      • any fractional seconds are REMOVED. PlsqlIndexByTable2.ORACLE_DATE_TO_CHAR_MASK is 'yyyy-mm-dd hh24:mi:ss' with no FF element, and TO_DATE('2019-03-01 14:25:36.123', ...) raises ORA-01830.

      A bare '2019-03-01' is accepted by that mask and means midnight, so a date-only value needs no padding and gets none.

      Dropping the fraction loses precision, and that is deliberate. An Oracle DATE has no sub-second component to store it in, so the alternative is not fidelity but ORA-01830 — and the SCALAR date path already drops it silently, by binding a Date that Oracle truncates on arrival. Refusing here would make an index-by DATE stricter than a plain DATE parameter beside it, for nothing. The emitted tool description says so out loud, because an accepted spelling that a caller cannot discover is precisely the defect a live MCP session reported against this area once already.

      Nothing else is touched. A value carrying a zone, or one that is not a timestamp at all, is passed through to be refused by Oracle in Oracle's own words rather than quietly reshaped into something that parses — the same division of labour PlsqlIndexByTable2.ensureFractionalSeconds() keeps.

      Parameters:
      theIsoText - the caller's value, or null
      Returns:
      the same value in the form the DATE mask accepts
      Since:
      2.0.6

    • toOracleUnzonedTimestampText

      public static String toOracleUnzonedTimestampText(String theIsoText)
      ISO text to the form the unzoned Oracle TIMESTAMP mask matches.

      The T becomes a space, and a fraction longer than 8 digits is truncated. Measured on 12c against 'yyyy-mm-dd hh24:mi:ss.ff8': a MISSING fraction is accepted, one to eight digits are accepted, and nine raises ORA-01830.

      That first point is why there is no counterpart to PlsqlIndexByTable2.ensureFractionalSeconds() here. That method exists because the ZONED mask stops tolerating a missing fraction once it names a zone; the unzoned mask never stopped, so padding would be work with no effect.

      Truncating rather than refusing, because a caller sending a ninth digit is already sending more precision than the column will keep: Oracle's TIMESTAMP tops out at 9 and DEFAULTS to 6, and rounds .12345678 to .123457 on arrival whatever we hand it.

      Parameters:
      theIsoText - the caller's value, or null
      Returns:
      the same value in the form the unzoned TIMESTAMP mask accepts
      Since:
      2.0.6

    • parse

      public static Date parse(Object theValue)
      Read one of the accepted forms.
      Parameters:
      theValue - the caller's value; toString is used, so a JSON string arrives here as itself
      Returns:
      the parsed value, or null for a null input
      Throws:
      IllegalArgumentException - naming the accepted forms, when the text is not one of them

    • parseTimestamp

      public static Timestamp parseTimestamp(Object theValue)
      The same as parse(java.lang.Object), as a Timestamp.
      Parameters:
      theValue - the caller's value, or null
      Returns:
      the parsed value, or null for a null input

    • parseSqlTimestamp

      public static Timestamp parseSqlTimestamp(Object theValue)
      Read a Timestamp from either the JDBC escape form "yyyy-mm-dd hh:mm:ss[.f...]" or one of the ISO forms parse(java.lang.Object) accepts.

      This exists because a generated table's TIMESTAMP column crosses MCP as the FIRST of those and a PL/SQL TIMESTAMP parameter crosses as the second, and one surface -- a table's index-lookup tool -- has to take a value the caller most likely read back off the other table tools. A generated row exposes such a column through set<Col>(String) / get<Col>String(), both of which speak java.sql.Timestamp's own toString/valueOf form, so that is what a get_by_pk result carries and what an insert accepts. Refusing it in the lookup tool alone would mean a value this server had just emitted was not a value it would take back.

      The JDBC form is read with Timestamp.valueOf(java.lang.String), not through parse(java.lang.Object): parse returns a Date and would truncate to milliseconds, while an Oracle TIMESTAMP(6) carries microseconds and valueOf keeps all nine digits of the nanosecond field.

      Parameters:
      theValue - the caller's value, or null
      Returns:
      the parsed value, or null for a null (or empty) input
      Throws:
      IllegalArgumentException - naming the accepted ISO forms, when the text is neither