Hi all, quick question: is is possible to define a DATE(x) over a datetime field? (in the domain I have Date x, and DATE(x) is only the yyyy-mm-dd part, not the full datetime)

after grails 3, I believe you can use LocalDateTime for datetime field mapping. I used it for a long while

how does that create an index from DATE(x) in the database?

I think you have to explicitly create the index if you need one

My question is how to create the index not how the define the field

startTime index: 'idx_event_start_time'

Emm, is this what you ask ??

the index needs to be the DATE part, I know how the create the index on the datetimefield

okay. this sounds more like a db question instead of grails question.

I mean, whats the DSL for something like this:

CREATE INDEX x_idx ON your_table ((DATE(x)));

I feel like you some how miss used Grails or Gorm, generally if i need extra index, I will do it in the migration file. but do ask people around here.

I'm not sure, I'm just trying to do something that is possible in the DBMS and trying to follow the Grails docs https://docs.grails.org/6.0.0/ref/Database%20Mapping/indexColumn.html

changeSet(id: 'create-date-index-dvdatetime', author: 'Pablo Pazos') { sql("ALTER TABLE dv_date_time_index ADD INDEX value_dt_idx ((DATE(value)))") } I ended up doing that manually in the charge log, though I hope there's some DSL to do that inside my model

With your initial question, you can use

static mapping = { someDate formula: “DATE(some_date_time) }

. This makes GORM include that formula in the query for the domain object. Hibernate does not support declaring indexes on expression/formulas. Just have your schema setup process run the DDL.

But I want the field to be datetime not date, I just need the index to be on DATE(). It's a functional index in MySQL.

Ah. I had missed that you rephrased your initial question. To clarify, I was saying two things. First, if you want the Domain to map an SQL expression like

DATE(timestamp_field)

or

price * qty

into a field, you can use “formula” on mapping closure:

class YourTable {
    static mapping = {
        dateSubmitted formula: "DATE(instant_submitted)" // note the underscore. This is interpreted by SQL database.
    }

    int id
    Date instantSubmitted // this is a column
    LocalDate dateSubmitted // this is not a column, because of formula above.
}

def row = YourTable.findById(1)
// SQL executed is something like: SELECT id, instant_submitted, DATE(instant_submitted) AS date_submitted_1 FROM your_table WHERE id = ?

row.instantSubmitted // YYYY-MM-DD HH:mm:SS
row.dateSubmitted // YYYY-MM-DD

Now, this doesn’t answer your later question about functional index. I was trying to address that in the latter half of my message. Hibernate (and by extension GORM) only supports referencing column names for indexes. You cannot declare an index based on expressions or functions. So, to create such indexes, it has to done manually, either in the DB or with Liquibase/Flyway. Here is the relevant SO answer that I found about this: https://stackoverflow.com/questions/33766647/jpa-2-1-index-annotation-for-columns-with-a-function