search

happytomatoe / hibernate-sqlite

Automatically exported from code.google.com/p/hibernate-sqlite
0 stars 0 forks source link

Date extracting functions #2

Open GoogleCodeExporter opened 9 years ago

GoogleCodeExporter commented 9 years ago 
Hi!

I'd like to propose adding the following functions to the SQLite dialect:

        registerFunction("second", new SQLFunctionTemplate(Hibernate.INTEGER,
"cast (strftime(\"%S\", ?1/1000, 'unixepoch', 'localtime') as integer)"));
        registerFunction("minute", new SQLFunctionTemplate(Hibernate.INTEGER,
"cast (strftime(\"%M\", ?1/1000, 'unixepoch', 'localtime') as integer)"));
        registerFunction("hour", new SQLFunctionTemplate(Hibernate.INTEGER, "cast
(strftime(\"%H\", ?1/1000, 'unixepoch', 'localtime') as integer)"));
        registerFunction("day", new SQLFunctionTemplate(Hibernate.INTEGER, "cast
(strftime(\"%d\", ?1/1000, 'unixepoch', 'localtime') as integer)"));
        registerFunction("month", new SQLFunctionTemplate(Hibernate.INTEGER,
"cast (strftime(\"%m\", ?1/1000, 'unixepoch', 'localtime') as integer)"));
        registerFunction("year", new SQLFunctionTemplate(Hibernate.INTEGER, "cast
(strftime(\"%Y\", ?1/1000, 'unixepoch', 'localtime') as integer)"));

They extract miscellanous fields from TIMESTAMP.
Result must be casted to integer, otherwise e.g. year(my_date)=2009 will
always return false, even if function type would be declared as
Hibernate.STRING.

Kind regards
Lukasz

Original issue reported on code.google.com by luke.sko...@gmail.com on 24 Nov 2009 at 1:22