invalid date in text column

[sbvitok]

Issue Summary

I read logs from ClickHouse via Redash and have a problem with invalid date message in text column.

SQL select datetime, pod_name, message from logs.raw where datetime >= '{{ Date.start }}' and datetime <= '{{ Date.end }}' and app_name = '{{app_name}}' and (message {{message}}) order by datetime,timestamp desc limit 5000

output form postgres

Copy (select * from query_results) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER;

cat /tmp/test.csv

15,1,1,eaf14c48c5cc3321478014d91920db34,"select datetime, pod_name, message from logs.raw where datetime >= '2021-01-27 00:00:00' and datetime <= '2021-01-27 23:59:59' and app_name = 'pfm-admins/gloo' and (message like '%') order by datetime,timestamp desc limit 5000","{""rows"": [{""message"": ""2021-01-27T00:00:01.733983944+03:00 stderr F {\""level\"":\""error\"",\""ts\"":1611694801.7337992,\""logger\"":\""gloo.v1.event_loop.setup.v1.event_loop.syncer.kubernetes_eds\"",\""caller\"":\""kubernetes/eds.go:203\"",\""msg\"":\""upstream pfm-admins.vkp-system-sentry-web-9000-custom: port 9000 not found for service sentry-web\"",\""version\"":\""1.3.34\"",\""stacktrace\"":\""github.com/solo-io/gloo/projects/gloo/pkg/plugins/kubernetes.filterEndpoints\\n\\t/workspace/gloo/projects/gloo/pkg/plugins/kubernetes/eds.go:203\\ngithub.com/solo-io/gloo/projects/gloo/pkg/plugins/kubernetes.(*edsWatcher).List\\n\\t/workspace/gloo/projects/gloo/pkg/plugins/kubernetes/eds.go:121\\ngithub.com/solo-io/gloo/projects/gloo/pkg/plugins/kubernetes.(*edsWatcher).watch.func1\\n\\t/workspace/gloo/projects/gloo/pkg/plugins/kubernetes/eds.go:130\\ngithub.com/solo-io/gloo/projects/gloo/pkg/plugins/kubernetes.(*edsWatcher).watch.func2\\n\\t/workspace/gloo/projects/gloo/pkg/plugins/kubernetes/eds.go:157\""}"", ""pod_name"": ""gloo-6c7f79fd79-fcl7h"", ""datetime"": ""2021-01-27 00:00:01""},
...

output debug console from firefox:

Deprecation warning: value provided is not in a recognized RFC2822 or ISO format. moment construction falls back to js Date(), which is not reliable across all browsers and versions. Non RFC2822/ISO date formats are discouraged and will be removed in an upcoming major release. Please refer to http://momentjs.com/guides/#/warnings/js-date/ for more info.
Arguments: 
[0] _isAMomentObject: true, _isUTC: true, _useUTC: true, _l: undefined, _i: 2021-01-27T00:00:01.733983944+03:00 stderr F {"level":"error","ts":1611694801.7337992,"logger":"gloo.v1.event_loop.setup.v1.event_loop.syncer.kubernetes_eds","caller":"kubernetes/eds.go:203","msg":"upstream pfm-admins.vkp-system-sentry-web-9000-custom: port 9000 not found for service sentry-web","version":"1.3.34","stacktrace":"github.com/solo-io/gloo/projects/gloo/pkg/plugins/kubernetes.filterEndpoints\n\t/workspace/gloo/projects/gloo/pkg/plugins/kubernetes/eds.go:203\ngithub.com/solo-io/gloo/projects/gloo/pkg/plugins/kubernetes.(*edsWatcher).List\n\t/workspace/gloo/projects/gloo/pkg/plugins/kubernetes/eds.go:121\ngithub.com/solo-io/gloo/projects/gloo/pkg/plugins/kubernetes.(*edsWatcher).watch.func1\n\t/workspace/gloo/projects/gloo/pkg/plugins/kubernetes/eds.go:130\ngithub.com/solo-io/gloo/projects/gloo/pkg/plugins/kubernetes.(*edsWatcher).watch.func2\n\t/workspace/gloo/projects/gloo/pkg/plugins/kubernetes/eds.go:157"}, _f: undefined, _strict: undefined, _locale: [object Object]
S/<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:20:14312
At/</<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:20:32490
At/<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:20:32541
At@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:20:32831
Tt@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:20:33208
d@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:20:11877
value/</<@https://redash.vkp-devkube.i/static/app.99a1c589f0eee5d6f913.js:1:327667
so/<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:9:27872
Kr@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:9:17738
e/dr.forOwn@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:9:57265
value/<@https://redash.vkp-devkube.i/static/app.99a1c589f0eee5d6f913.js:1:327549
Kt@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:9:5219
Ua@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:9:40178
value@https://redash.vkp-devkube.i/static/app.99a1c589f0eee5d6f913.js:1:327500
value/<@https://redash.vkp-devkube.i/static/app.99a1c589f0eee5d6f913.js:1:330630
e/</k[t]/F<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:216:4397
s/</<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:105358
s/<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:105448
$eval@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:112444
$digest@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:110727
$apply@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:112727
m/y</</<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:60936
C@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:63790
Nn/this.$get</</</y.onload@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:63354
EventHandlerNonNull*Nn/this.$get</</<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:63190
m/y</<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:60796
m/y<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:61447
s/</<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:105358
s/<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:105448
$eval@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:112444
$digest@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:110727
$apply@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:112727
m/y</</<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:60936
C@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:63790
Nn/this.$get</</</y.onload@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:63354
EventHandlerNonNull*Nn/this.$get</</<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:63190
m/y</<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:60796
m/y<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:61447
s/</<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:105358
s/<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:105448
$eval@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:112444
$digest@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:110727
$apply@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:112727
a/c<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:120845
d@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:31426
tn/i.defer/n<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:32782
setTimeout handler*tn/i.defer@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:32757
a@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:120734
value/<@https://redash.vkp-devkube.i/static/app.99a1c589f0eee5d6f913.js:1:331384
e/</k[t]/F<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:216:4397
s/</<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:105358
s/<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:105448
$eval@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:112444
$digest@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:110727
$apply@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:112727
m/y</</<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:60936
C@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:63790
Nn/this.$get</</</y.onload@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:63354
EventHandlerNonNull*Nn/this.$get</</<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:63190
m/y</<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:60796
m/y<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:61447
s/</<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:105358
s/<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:105448
$eval@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:112444
$digest@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:110727
$apply@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:112727
a/c<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:120845
d@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:31426
tn/i.defer/n<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:32782
setTimeout handler*tn/i.defer@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:32757
a@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:120734
value/<@https://redash.vkp-devkube.i/static/app.99a1c589f0eee5d6f913.js:1:331384
e/</k[t]/F<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:216:4397
s/</<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:105358
s/<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:105448
$eval@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:112444
$digest@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:110727
$apply@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:112727
m/y</</<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:60936
C@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:63790
Nn/this.$get</</</y.onload@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:63354
EventHandlerNonNull*Nn/this.$get</</<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:63190
m/y</<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:60796
m/y<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:61447
s/</<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:105358
s/<@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:105448
$eval@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:112444
$digest@https://redash.vkp-devkube.i/static/vendors~app.22941359f2e6f98e80a1.js:198:110727
moment.js:293

Technical details:

• Redash Version: redash/redash:8.0.2.b37747
• Browser/OS: ArchLinux Firefox, ArchLinux Chromium, MacOS Safari, MacOS Chrome, and also all in privet mode without any caches
• How did you install Redash: helm chart I have the same configuration in dev and prod kubernetes cluster CentOS 7, kubernetes v1.18.1, redash 8.0.2.b37747, postgres 10.8, CH 20.3.16.165 and problem only in dev environment in production all works good. In dev environment at first (8.0.0.b32245) also was no problem. But after some work appeared this message on the same version (8.0.0.b32245) without any update. I try reinstall redash and DB but no result.

[susodapop]

Redash uses MomentJS for date handling. Clickhouse is returning dates in this format 2021-01-27T00:00:01.733983944+03:00 which is not recognized by Moment.

[sbvitok]

Redash uses MomentJS for date handling. Clickhouse is returning dates in this format 2021-01-27T00:00:01.733983944+03:00 which is not recognized by Moment.

"message" column is sting.

CREATE TABLE logs.raw (`stream` String, `message` String, `namespace` String, `deploy_name` String, `node_name` String, `pod_name` String, `container_name` String, `app_name` String, `date
time` DateTime, `timestamp` Float64, `offset` Int64)

and it's 2021-01-27T00:00:01.733983944+03:00 just time format from app logs, which is in the log itself

[kravets-levko]

@sbvitok If you want to show that value as is, edit this table ("Edit Visualization" button) and change "Display as" for that column

[sbvitok]

@sbvitok If you want to show that value as is, edit this table ("Edit Visualization" button) and change "Display as" for that column

Don't work. I try execute query again , after set "Display as", but no changes.

[kravets-levko]

Column datetime is still displayed as Date/Time - so I see no changes. Also, do you see any error messages in browser console?

[sbvitok]

Column datetime is still displayed as Date/Time - so I see no changes. Also, do you see any error messages in browser console?

with datetime column no problem. why need to change its data type? problem in message column after set "Display as" to Text instead of log body I have NaN without any error in concole

[kravets-levko]

Oh, right, I misunderstood the issue 🤦 I'll try to reproduce this issue and then will get back to you

[kravets-levko]

Okay. So, I managed to reproduce it, and yeah, that's a bug - regular expressions we use to detect date/time string have anchor on beginning, but allow any garbage at the and, but Moment fails to parse such strings.

Related to: getredash/redash#3553

https://github.com/getredash/redash/blob/04edf16ed42cdf9d005e7a512162cee7ca298c9c/client/app/services/query-result.js#L150-L155

[kravets-levko]

As a temporary solution you can try to prepend any character to the beginning of your log strings (e.g. whitespace) - if will prevent this code from messing up the data.

[sbvitok]

As a temporary solution you can try to prepend any character to the beginning of your log strings (e.g. whitespace) - if will prevent this code from messing up the data.

Yes, that's how I solved the problem.

[man0xff]

you cat just concat space to message :)

select concat(' ', message) from ...

[harishaaram]

Is this resolved in the frontend? I am still seeing a invalid date when do I snowflake select query through redash. With concat I am getting the proper string value(eg: 2021-05-18T15:49:16.54Z#1aaf1b6d-b7f0-11eb-b4e2-da0a7baaae) My redash browser Version: 8.0.0+b32245