The date/time and conversion functions available are also summarized by http://monetdb.cwi.nl/SQL/Documentation/SQL-Features.html The example below can be performed using the date/time functions and proper grouping. datetime_func: EXTRACT ( datatime_field FROM scalar_exp ) | CURRENT_DATE [()] | CURRENT_TIME [()] | CURRENT_TIMESTAMP [()] | LOCALTIME [()] | LOCALTIMESTAMP [()] | CURRENT_TIME [()] datetime_field: { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } Kirk Abbott wrote:

Martin,

Thanks for your reply. I am using the sql frontend only.

I just had a look at the functions table and now see all the string/data extraction primitives. I am particularly interested in mimicking the PostgreSQL functionality (which is our main transactional/etl system).

BTW, we typically want to do ad-hoc stuff like:

...

select to_char(charge_date, 'yyyy-mm') , sum(actual_consump_adj) , sum(billed_consump_adj) , count(*) from consumption group by to_char(charge_date, 'yyyy-mm') order by to_char(charge_date, 'yyyy-mm') ;

Cheers, Kirk.

Martin Kersten wrote:

...

Dear Kirk,

Thank you for your interest in using MonetDB. Can you be more specific on the front-end you are using, e.g. SQL or XQuery. The SQL comes with several string/date extraction primitieves. It may help to understand your request by excemplifying the reference system you have in mind, e.g. MySQL, PostgreSQL,...

regards, Martin

Kirk Abbott wrote:

...

Hello,

We are trying to move some data over to Monetdb and are finding that we are missing some of our favorite formatting functions (to_date, to_char etc.). Does Monetdb have a user defined functions extension framework? If so, we would try to mimic these functions there.

Thanks, Kirk.

------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Thanks much Stefan, I will try the 'internal functions' first and then follow-up if those don't do what I expect them to. Cheers, Kirk. Stefan Manegold wrote:

Hello Kirk,

according to our documentation MonetDB/SQL supports both user defined functions follwoing the SQL standard: http://monetdb.cwi.nl/SQL/Documentation/Functions.html and binding externally (in MAL) defined functions into SQL: http://monetdb.cwi.nl/SQL/Documentation/External-Functions.html

Would either help you further?

Stefan

On Wed, Sep 03, 2008 at 07:35:25AM -0500, Kirk Abbott wrote:

...

Hello,

We are trying to move some data over to Monetdb and are finding that we are missing some of our favorite formatting functions (to_date, to_char etc.). Does Monetdb have a user defined functions extension framework? If so, we would try to mimic these functions there.

Thanks, Kirk.

------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Dear Kirk, Through the JDBC connection you import all the data from the server and have full control over the formatting. Formatting at the server side is/should be limited. But,perhaps I am missing a point here. regards, Martin Kirk Abbott wrote:

Hello All,

Continuing with the discussion related to user_defined functions and in particular to the current problem of formatting... Java 1.5 has extensive capabilities to formatting through the java.util.Formatter class

@see http://java.sun.com/j2se/1.5.0/docs/api/java/util/Formatter.html

Has anyone worked on getting Monetdb to access Java? If not Java, where is there some info on the interface to C?

Thanks, Kirk

Stefan Manegold wrote:

...

Hello Kirk,

according to our documentation MonetDB/SQL supports both user defined functions follwoing the SQL standard: http://monetdb.cwi.nl/SQL/Documentation/Functions.html and binding externally (in MAL) defined functions into SQL: http://monetdb.cwi.nl/SQL/Documentation/External-Functions.html

Would either help you further?

Stefan

On Wed, Sep 03, 2008 at 07:35:25AM -0500, Kirk Abbott wrote:

...

Hello,

We are trying to move some data over to Monetdb and are finding that we are missing some of our favorite formatting functions (to_date, to_char etc.). Does Monetdb have a user defined functions extension framework? If so, we would try to mimic these functions there.

Thanks, Kirk.

------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users