[Bug] Timezone discrepancy when exporting CSV files from discovery and dashboard

[psanz-estc]

Kibana version: 7.5

Describe the bug:
In Kibana (7.5), when exporting data from discovery into CSV, the timestamp field in the CSV file is aligned with the timestamp field shown in discovery (more in the thread)

But when I create a dashboard with the same saved search, and then export the CSV file from there, the timestamp in the CSV file is in UTC

Timezone in Kibana is set to Madrid

Looking at the queries, and it seems the date range takes into consideration the timezone, but the time conversion scripted field is not passed:

1. Export via “Copy POST URL”:

https://xxxxxx.us-central1.gcp.cloud.es.io:9243/api/reporting/generate/csv?jobParams=(conflictedTypesFields:!(),fields:!(%27@timestamp%27,_id,_index,_score,_type,agent,bytes,clientip,event.dataset,extension,geo.coordinates,geo.dest,geo.src,geo.srcdest,host,hour_of_day,index,ip,machine.os,machine.ram,memory,message,phpmemory,referer,request,response,tags,timestamp,url,utc_time),indexPatternId:%2790943e30-9a47-11e8-b64d-95841ca0b247%27,metaFields:!(_source,_id,_type,_index,_score),searchRequest:(body:(_source:(excludes:!()),docvalue_fields:!((field:%27@timestamp%27,format:date_time),(field:timestamp,format:date_time),(field:utc_time,format:date_time)),query:(bool:(filter:!((match_all:()),(range:(timestamp:(format:strict_date_optional_time,gte:%272020-01-30T09:53:28.177Z%27,lte:%272020-01-30T10:08:28.177Z%27)))),must:!(),must_not:!(),should:!())),script_fields:(hour_of_day:(script:(lang:painless,source:%27doc%5B!%27timestamp!%27%5D.value.withZoneSameInstant(ZoneId.of(!%27Europe%2FBerlin!%27)).getHour()%27))),sort:!((_score:(order:desc))),stored_fields:!(%27*%27),version:!t),index:kibana_sample_data_logs),title:MyTest,type:search)

2. “Export CSV” in the dashboard:

https://xxxxxx.us-central1.gcp.cloud.es.io:9243/api/reporting/v1/generate/immediate/csv/saved-object/search:746e6680-4348-11ea-8a21-af2e91cbed41' --data-binary '{"timerange":{"min":"2020-01-30T10:55:00+01:00","max":"2020-01-30T11:10:00+01:00","timezone":"Europe/Madrid"},"state":{"sort":[{"_score":{"order":"desc"}}],"docvalue_fields":[{"field":"@timestamp","format":"date_time"},{"field":"timestamp","format":"date_time"},{"field":"utc_time","format":"date_time"}],"query":{"bool":{"must":[],"filter":[{"match_all":{}},{"match_all":{}},{"range":{"timestamp":{"format":"strict_date_optional_time","gte":"2020-01-30T09:55:00.703Z","lte":"2020-01-30T10:10:00.703Z"}}}],"should":[],"must_not":[]}}}}

Expected behavior:
Both CSV export files should be aligned with the timezone configuration.

[elasticmachine]

Pinging @elastic/kibana-reporting-services (Team:Reporting Services)

[tsullivan]

I had a draft of a branch to fix this issue, which would have solved the problem by including timezone as a parameter to generate the CSV from a dashboard panel download link. I ended up closing it, because since we know the API request is going to a Kibana instance, the instance can just get the timezone from Advanced Settings internally.

It turns out, that is how export CSV from Discover works: internally, the timezone is part of other UI settings that get looked up internally: https://github.com/elastic/kibana/blob/114a0a1/x-pack/legacy/plugins/reporting/export_types/csv/server/execute_job.ts#L99

Getting to the basics of this issue was difficult due to a huge amount of indirection in the internals of the Download CSV logic. There are several functions named generateCsv* which were added in the initial PR: #34571. At the time the indirection was a plan to add more download capabilities to this feature.

I've picked back on this issue. It seems clear the first changes should be to remove layers of indirection in the CSV generation. Draft PR: #67027

[tsullivan]

duplicate of #56153