Improve Date types

[TonyGravagno]

Zod supports z.date(). mysql-zod saves date/time as z.string().

[TonyGravagno]

This one is pretty big. I've documented my current plan in the README below. I will probably tackle one type at a time because each one has nuances and a single PR for all types would be too big. That said, I've already completed a lot of it and will PR progressive chunks from branches named tg-datetime-00.

---

Config

{
  "host": "127.0.0.1",...
  "datetime": {
    "useDate": false,
    "useIso": true,
    "coerce" : false,
    "offset": true,
    "precision": 3,
    "minDate": "1900-01-01",
    "maxDate": "2099-12-31",
    "year" : "/^\\d{4}$/"
  }
}

Option	Description
...	...
datetime	See notes on Date and Time types below. If present, this object refines how these types are converted to Zod schema. See example above and details below.

Date and Time types

MySQL date and time types include DATE, DATETIME, TIMESTAMP, and YEAR. MySQL accepts, stores, and returns different formats for dates and times. Years may be 1-4 digits or a string. Time may or may not have sub-second precision. User data from a browser form or other sources may be a string or Date object, and subject to other formats. Even if the database accepts, for example, a 2 digit year for a YEAR type in the range of 0 to 99, application rules may require values to be provided as 4 digit years or from 2020 to 2030. You may not wish data coming from from the database to validate in the context of your application, even if the database somehow has those values already stored.

MySQL-Zod defaults to validating with a single rule z.string() for all date and time fields in all processed tables. This is because no context is processed from the database, and there is no other means to differentiate intent for different fields with the same data type. Until this utility is enhanced to process constraints, mysql-zod.json options allow you to set somewhat better default rules than z.string(), based on your understanding of your data. No matter what the default, z.string() or custom Zod schema, manual changes to generated code for individual tables and fields will almost certainly be required.

datetime options	Description
useDate	Boolean/true = z.date() to validate a Date object. ref1
useIso	Boolean/true = z.string().datetime() to validate a string representation of an ISO-formatted date+time. ref2
	useDate and useIso are mutually exclusive. If they are both true, a false value will be used for both at run-time.
coerce	Boolean applies coersion for all date types. That is, values are converted to primitives before processing them as z.string, z.number, or z.date. For example, with z.coerce.date() a string will first be converted from a string to a valid date object before other date-related processing. ref3
offset	Boolean = true results in z.string().datetime({ offset: true }). (useIso only)
precision	Numeric 0-6 adds a precision constraint to datetime(). (useIso only)
minDate	String adds a minumum constraint to .date() or .datetime(). Example: z.date().min(new Date("1900-01-01")) Note : Without server-specific settings, the minimum year for a TIMESTAMP datatype is 1970. The maxDate value should be less than or equal to that year. ref4
maxDate	String adds a maximum constraint to .date() or .datetime(). Example: z.date().max(new Date("2099-12-31")) Note : Without server-specific settings, the maximum year for a TIMESTAMP datatype is 2038. The maxDate value should be less than or equal to that year. ref4
year	MySQL supports multiple formats for the YEAR data type (ref5) including strings and numbers. If the year option is not present, the default Zod schema is z.string(). If present, this year option may be a regular expression in the format "/.../" or the value "number". If a RegExp, the validation applies the RegExp to string input. For example, "/^\\d{2}$/" validates a string as consisting of two digits. For "year": "number", only a number will validate, not a string containing a number.

References

1) Zod.dev Dates 1) Zod.dev ISO datetimes 1) Zod.dev Coercion for primitives 1) MYSQL DATE, DATETIME, and TIMESTAMP Types 1) MySQL YEAR type

[thelinuxlich]

I think you overcomplicated this. A much better approach would be letting the user programmatically define which zod schema for each field type