Re: easiest way to coerce "?m/?d/yyyy" strings to a date in monetdb?

hi richard, a proper strptime call would not be sensitive to field width, the problem is the windows version of monetdb does not have strptime. :/ is there a [find] equivalent in monetdb? otherwise i'm not sure how this could be translated into something that solves my problem? thanks On Mon, May 18, 2015 at 12:44 PM, Richard Wesley wrote:

Anthony -

The way we sometimes have to implement this stuff in Tableau's compilation layer is to use string search, substring extraction, casting and date arithmetic. It's not pretty, but it works. So something like

[dd] := int( left( [string], find( [string], '/') ) ) [mm] := int( mid( [string], find( [string], '/'), find( [string], '/', 1 + find([string], '/') ) ) ) [yyyy] := int( mid( [string], find( [string], '/', 1 + find([string], '/') ) ) ) [date] := dateadd( 'day', [dd]-1, dateadd('month', [mm]-1, dateadd( 'year', [yyyy]-1900, #1900-01-01# ) ) )

I know this isn't MonetDB syntax, but hopefully it gives you the idea. And converting things to simple scalars as quickly as possible tends to be faster, especially when you have datetime data that does not compress nicely.

I am surprised that the strptime format is sensitive to field width. I'm pretty sure the MySQL implementation is not...

Niels & co. -

Converting strings to date scalars is one of the most common sources of nasty string calculations we see on Tableau Public. It is so bad that about 18 months ago we added a strptime-like function to the language to help our users solve annoying ETL problems like Anthony's. Data from Public also suggests that there are an endless number of date formats in the world (I've extracted nearly 600 so far) so I think it would be really useful if MonetDB could include cross-platform support for a date parsing language like strptime or ICU's time parsing (which is what we embedded in the Tableau Data Engine.) I know MonetDB is not an ETL tool, but some stuff is so common that it is worth making everyone's life easier.

On May 18, 2015, at 06:06 , Anthony Damico wrote:

so.. other suggestions to solve my problem? :) i can probably do this with a bunch of horrible CASE statements, but i'm hoping for a more idiomatic strategy. thanks!

On Mon, May 18, 2015 at 9:01 AM, Niels Nes wrote:

...

On Mon, May 18, 2015 at 08:38:03AM -0400, Anthony Damico wrote:

...

thanks Niels! so you are saying this command should work, correct? it currently does not..

"SELECT str_to_date( '1/1/2014' , '%m/%d/%Y' ) as date"

if i am on windows, do i need to install strptime separately? i am currently hitting this error that (from the version tracker) seems like it should've been resolved? on windows this maybe a problem indeed. strptime etc support doesn't exist on windows.

Niels

...

Error in .local(conn, statement, ...) : Unable to execute statement 'SELECT str_to_date( '1/1/2014' , '%m/%d/ %Y' ) as date'. Server says '!strptime support missing'.

http://permalink.gmane.org/gmane.comp.db.monetdb.scm/36127

thank you :)

On Mon, May 18, 2015 at 8:09 AM, Niels Nes wrote:

On Mon, May 18, 2015 at 07:17:28AM -0400, Anthony Damico wrote: > hi, i have a VARCHAR column with dates like > > "1/1/2014"

Anthony

We have the following date/string conversion functions

create function str_to_date(s string, format string) returns date external name mtime."str_to_date";

create function date_to_str(d date, format string) returns string external name mtime."date_to_str";

create function "convert"(s string, format string) returns date external name mtime."str_to_date";

Its based on the unix strftime/strptime (so for the format look at those).

Niels > > and also > > "12/31/2014" > > since there are no leading zeroes, the months and days are in different > positions so i cannot use something like this to CAST it into a date > type. > > > CAST( SUBSTRING( x , 7 , 10 ) || '-' || SUBSTRING( x , 1 , 2 ) || '-' | > | SUBSTRING( x , 4 , 5 ) AS DATE ) AS y > > > could someone offer advice to convert this string field to date in > monetdb? thanks!!

> _______________________________________________ > users-list mailing list > users-list@monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list

-- Niels Nes, Manager ITF, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: https://www.cwi.nl/people/niels e-mail: Niels.Nes@cwi.nl

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

...

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

-- Niels Nes, Manager ITF, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: https://www.cwi.nl/people/niels e-mail: Niels.Nes@cwi.nl

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

Best regards, ------------------------------------------------- Richard Wesley Research Scientist Tableau Software

t: 206.633.3400 x5249 f: 206.633.3004 e: hawkfish@tableau.com

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list