1

I have some files that using different date types:

some only have year "2020", others have month "2020-12", and still, some have date:"2020-12-21"

So I want to use interval. Like this:

(define-table test-date ()
  ((date :col-type :interval
         :initarg :date
         :accessor date)))
(create-dao 'test-date
            :date "2020 years")
(date (find-dao 'test-date))=>((:MONTHS 24240) (:DAYS 0) (:SECONDS 0) (:USECONDS 0))

Q1. Is this is how interval supposed to use?

Q2. How to get year?

C-Entropy
  • 303
  • 2
  • 9

1 Answers1

0

Your use of interval is smelly. Look, you store a year and you are getting a duration in months. interval is used to store durations, and is particularly used in date arithmetic: date + INTERVAL expr unit. So I don't think you want to use it here to store a date format.

You could:

  • either store a date and transform an incomplete date to a full one: "2020" would become "2020-00-00" if that is acceptable for your logic,
  • either store a string and use a validator function of your own (you could use Mito's inflation/deflation mechanism to retrieve your data in the right data format),
  • either use more date fields (year, month, day…) that are nullable.

Recipes for dates and time manipulation: https://lispcookbook.github.io/cl-cookbook/dates_and_times.html

Ehvince
  • 17,274
  • 7
  • 58
  • 79
  • Thanks you! I have tried `(deftable test-date () ((date :col-type :date :initarg :date :accessor date))) (ensure-table-exists 'test-date) (create-dao 'test-date :date "2020-00-00")` but it gives error:`DB Error: date/time field value out of range: "2020-00-00" (Code: 22008)` How should I store it as date? – C-Entropy Mar 21 '21 at 15:16
  • Could you give some example code? Please. – C-Entropy Mar 21 '21 at 15:17
  • Your example works for me on SQLite (which can store dates as TEXT, REAL or INTEGER (unix time) values). What DB engine are you using? Storing "2020-00-00" may not work on MySQL( https://www.mysqltutorial.org/mysql-date/) – Ehvince Mar 21 '21 at 19:27
  • I'm using postgresql, according to [this](https://stackoverflow.com/questions/13780481/storing-year-in-database), date in postgresql may simply can't store year only. – C-Entropy Mar 22 '21 at 03:03