string of format "YYYY-MM-DD hh:mm" not recognized as DATETIME

[terefang]

the query yields an empty string:

$ usql_static -C -c "select 0,UNIX_TIME('2020-01-01 12:12'),0 " 'csv:./'
Connected with driver csvq (CSVQ v1.18.1)
0,"UNIX_TIME('2020-01-01 12:12')",0
0,,0

$ usql_static -C -c "select 0,DATETIME('2020-01-01 12:12'),0 " 'csv:./'
Connected with driver csvq (CSVQ v1.18.1)
0,"DATETIME('2020-01-01 12:12')",0
0,,0

but this works

$ usql_static -C -c "select 0,DATETIME('2020-01-01 12:12:12'),0 " 'csv:./'
Connected with driver csvq (CSVQ v1.18.1)
0,"DATETIME('2020-01-01 12:12:12')",0
0,2020-01-01T12:12:12+01:00,0

[mithrandie]

The DATETIME function and other time related functions convert only some forms of strings by default. If you want to convert other formats, you can specify the format.

ADD '%Y-%m-%d %h:%i' TO @@DATETIME_FORMAT;
SELECT DATETIME('2020-01-01 12:12');
SELECT UNIX_TIME('2020-01-01 12:12');

Placeholders are the same as used in the DATETIME_FORMAT function.

[terefang]

The DATETIME function and other time related functions convert only some forms of strings by default. If you want to convert other formats, you can specify the format.

ADD '%Y-%m-%d %h:%i' TO @@DATETIME_FORMAT;
SELECT DATETIME('2020-01-01 12:12');
SELECT UNIX_TIME('2020-01-01 12:12');

Placeholders are the same as used in the DATETIME_FORMAT function.

oh ... did not notice you can add datetime formats to the parser

will this also work if two or more different formats are used in the dataset ?

can multiple format specs added to @@DATETIME_FORMAT ?

[terefang]

btw ... cheers for such a nice lib :+1:

[terefang]

just for your understanding i was comparing csvq funtionality to the sqlite ones

sqlite has support for 12 different formats by default https://www.sqlite.org/lang_datefunc.html#time_values

c.

[mithrandie]

Multiple formats can be added by executing multiple ADD TO statements.

[terefang]

looks like i am wrong in this repo, then csvq repo would be more proper ... anyway:

in github.com/mithrandie/csvq/lib/value/conv.go the following changes need to be made:

on line 85 from:

if t, e := time.ParseInLocation("2006-01-02T15:04:05.999999999", s, location); e == nil {
	return t, true
}

to:

if t, e := time.ParseInLocation("2006-01-02T15:04:05.999999999", s, location); e == nil {
	return t, true
} else if t, e := time.ParseInLocation("2006-01-02T15:04:05", s, location); e == nil {
	return t, true
} else if t, e := time.ParseInLocation("2006-01-02T15:04", s, location); e == nil {
	return t, true
}

and on line 90 from:

if t, e := time.ParseInLocation("2006-01-02 15:04:05.999999999", s, location); e == nil {
	return t, true
}

to:

if t, e := time.ParseInLocation("2006-01-02 15:04:05.999999999", s, location); e == nil {
	return t, true
} else if t, e := time.ParseInLocation("2006-01-02 15:04:05", s, location); e == nil {
	return t, true
} else if t, e := time.ParseInLocation("2006-01-02 15:04", s, location); e == nil {
	return t, true
}