23.6 Working with date fields

This section describes particular issues around using datetime database fields via Common SQL. Note: SQLite does not support date fields at all.

See also 23.9.9 Types of values returned from queries for information specifically about returning datetime values from MySQL.

23.6.1 Testing date values

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:

1. Common SQL cannot know that the field will be a date field until the results are returned, and:
2. the database probably does not know about Common Lisp universal time.

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.

23.6.2 DATE returned as universal time

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])

23.6.2.1 Timezone of returned DATEs

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.

23.6.3 DATE returned as string

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.

23.6.4 Using universal time format

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.