On Mon, May 18, 2015 at 3:34 PM, Niels Nes <Niels.Nes@cwi.nl> wrote:

On Mon, May 18, 2015 at 01:15:40PM -0400, Anthony Damico wrote:
> 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

Anthony

In a future version we will include a strptime for windows as I found
lots of open source (freebsd for example) usable versions. So it should
became available in the development branch soon.
For now this will not help you unfortunately.

Niels

>
> On Mon, May 18, 2015 at 12:44 PM, Richard Wesley <hawkfish@tableau.com>
> 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 <ajdamico@gmail.com>
> 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 <Niels.Nes@cwi.nl>
> 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 <
> Niels.Nes@cwi.nl> 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
>
>
>

> _______________________________________________
> 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