-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
On 18/05/15 21:39, Anthony Damico wrote:
well that is very very awesome, thank you!! is there any chance strptime can be included in the new SP4 that Sjoerd said would be released later this week? ;) and sorry to cause trouble. thanks for the amazing product
No chance, sorry. It'll take a bit longer than that to find a good and usable (both code-wise and license-wise) implementation and integrate that into our code. And anyway, such a change would only be allowed in a feature release.
On Mon, May 18, 2015 at 3:34 PM, Niels Nes <Niels.Nes@cwi.nl mailto: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 mailto: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 mailto: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'.
36127
thank you :)
On Mon, May 18, 2015 at 8:09 AM, Niels Nes <
Niels.Nes@cwi.nl mailto: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 mailto:sip%3A4098@sip.cwi.nl
url: https://www.cwi.nl/people/niels e-mail:
Niels.Nes@cwi.nl mailto:Niels.Nes@cwi.nl
_______________________________________________ users-list mailing list users-list@monetdb.org
_______________________________________________ users-list mailing list users-list@monetdb.org mailto: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
tel:%2B%2B31%2020%20592-4098 sip:4098@sip.cwi.nl mailto:sip%3A4098@sip.cwi.nl
url: https://www.cwi.nl/people/niels e-mail: Niels.Nes@cwi.nl mailto:Niels.Nes@cwi.nl
_______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org mailto: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 tel:206.633.3400%20x5249 f: 206.633.3004 tel:206.633.3004 e: hawkfish@tableau.com mailto:hawkfish@tableau.com
_______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org mailto: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 tel:%2B%2B31%2020%20592-4098 sip:4098@sip.cwi.nl mailto:sip%3A4098@sip.cwi.nl url: https://www.cwi.nl/people/niels e-mail: Niels.Nes@cwi.nl mailto:Niels.Nes@cwi.nl
_______________________________________________ users-list mailing list users-list@monetdb.org mailto: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
- -- Sjoerd Mullender