This section describes particular issues around using datetime database fields via Common SQL. Note: SQLite does not support date fields at all.
See also Types of values returned from queries for information specifically about returning datetime values from MySQL.
(sql:select * :from [Table] :where [= [Date] "2005-12-25"])
To convert between universal time and standard SQL DATE or TIMESTAMP string, you can use the functions encode-db-standard-date, encode-db-standard-timestamp, decode-to-db-standard-date and decode-to-db-standard-timestamp. Note that the database may have non-standard date format, in which case you will need to either format the string yourself, or on Oracle tell the database to use the standard format by passing date-string-format to connect.
(sql:select [MyDate] :from [MyTable] :where [= [id] 1])
Common SQL creates universal time values from DATE fields assuming that the database contains times in Coordinated Universal Time (UTC). That is, as if by passing time-zone 0 to
encode-universal-time. To decode the values consistently with this encoding, pass time-zone 0 to
(sql:select [MyDate :string] :from [MyTable] :where [= [id] 1])
See select for details.
If the database is only accessed via Common SQL and you want to use the universal time date format, then you might consider using an INTEGER column containing universal time values instead of a DATE column.
LispWorks User Guide and Reference Manual - 20 Sep 2017