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.
Compare DATE values by formatting the date as a string in a date format that the database can parse. For example:
(sql:select * :from [Table] :where [= [Date] "2005-12-25"])
Note that it is not possible to lookup date values in the database using numeric values. This is because:
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.
By default Common SQL converts DATE values to Common Lisp universal times. Therefore code like this returns Common Lisp universal times (that is, integers) where MyDate is a DATE field type:
(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 decode-universal-time.
If the database contains times in a different timezone, then the integer time-zone needs to be adjusted by adding an appropriate multiple of 3600 before calling decode-universal-time.
Instead of universal time integers, you can obtain strings formatted by the database by modifying the MyDate database identifier, adding :string like this:
(sql:select [MyDate :string] :from [MyTable] :where [= [id] 1])
This avoids the overhead of converting DATEs to universal times and so may improve performance of your application.
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