timezone conversion using sed&date instead of jq

Cloudera Manager shows all timestamp in local timezone like below. In this screen capture , timezone is PST as you see in red part. Here , CM is showing list of Impala queries executed in last 30 minutes.

image

But CM REST API returns them in UTC.

$ curl --silent -u un:pw \
  "http://cm_server:7180/api/v19/clusters/Cluster/services/IMPALA%2D1/impalaQueries?\
  from=2019-12-18T01:35:00-0800&to=2019-12-18T01:47:00-0800" |
  cut -c-80 | head
{
  "queries" : [ {
    "queryId" : "3a4e61d893fac33d:45db4c8900000000",
    "statement" : "CREATE TABLE default.my_first_table2 (   id BIGINT NOT NULL E
    "queryType" : "DDL",
    "queryState" : "FINISHED",
    "startTime" : "2019-12-18T09:35:05.220Z",
    "endTime" : "2019-12-18T09:46:29.203Z",
    "rowsProduced" : null,
    "attributes" : {

So I tried to convert UTC to PST. Since data is JSON, I tried to do it with jq first. But I think I hit the issue below.

nanoseconds field; %f is not available in standard strptime

bash – Strptime with timezones and jq – Stack Overflow

So, I used sed and date. You can see timestamp is same with the ones in above screenshot’s green part.

$ TZ=America/Los_Angeles curl --silent -u un:pw \
  "http://cm_server:7180/api/v19/clusters/Cluster/services/IMPALA%2D1/impalaQueries?\
  from=$(echo -n "$(date -d "today 01:35" -Isec)"|jq -s -R -r @uri)&\
  to=$(echo -n "$(date -d "today 01:47" -Isec)"|jq -s -R -r @uri)" |
  cut -c-80 | head |
  sed -r \
  's/^    "(start|end)Time" : "(.*)",/\
(echo -n "    \\"\1Time\\" : \\"";date -d "\2" -Ins|tr -d "\\n";echo -n "\\",")\
/e'
{
  "queries" : [ {
    "queryId" : "3a4e61d893fac33d:45db4c8900000000",
    "statement" : "CREATE TABLE default.my_first_table2 (   id BIGINT NOT NULL E
    "queryType" : "DDL",
    "queryState" : "FINISHED",
    "startTime" : "2019-12-18T01:35:05,220000000-0800",
    "endTime" : "2019-12-18T01:46:29,203000000-0800",
    "rowsProduced" : null,
    "attributes" : {

This  is ugly and slow. This invokes 2 processes(bash, date) for every timestamp line. So, if you don’t need millisecond precision, it may be better doing it in jq.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s