Skip to main content
HansH
HansH
Gold ⭐️
October 13, 2020
Solved

Specify timezone when parsing CSV data?

Hello,

 

we are using Apache NiFi to process CSV data that contains time stamps without a specified time zone.  By default, it seems that NiFi considers timestamps parsed by CSVReader with a parse specification like "yyyy-MM-dd HH:mm:ss" to be in UTC, no matter what the user.timezone property of the JVM or the TZ environment variable is set to.  Is there a way to force the parsing to happen in a specific or the system time zone instead?

 

Thanks,

Hans

HansH
HansHAnswer
Gold ⭐️
October 16, 2020

After I have not been able to find a solution that would be easy to implement inside of NiFi, I've written a small perl (yuk) script that can be used to adjust timestamps in a CSV file to be in ISO8601 format.  Maybe it is useful to someone else:

#!/bin/perl -w

# This perl script adds timezone information to timestamps without a
# timezone.  All timestamps in the input file that follow the format
# "YYYY-MM-DD HH:MM:SS" are converted to ISO8601 timestamps.

use strict;

use DateTime::Format::Strptime;

my $time_zone = 'Europe/Amsterdam';
my $parser = DateTime::Format::Strptime->new(
    pattern   => '%Y-%m-%d %T',
    time_zone => $time_zone
    );
my $printer = DateTime::Format::Strptime->new(
    pattern   => '%FT%T%z',
    time_zone => $time_zone
    );

while (<>) {
    s/(?<=")(\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d)(?=")/
        my $dt = $parser->parse_datetime($1);
        $printer->format_datetime($dt);
    /ge;
    print;
}

 

View in thread

2 replies

stevenmatison
stevenmatison
Platinum ⭐️⭐️
October 13, 2020

@HansH   I am not sure if this helps but just yesterday i had to use the argument in the reader jdbc connection string like this:

 

?serverTimezone=UTC

 

 

Translate
HansH
HansHAuthorAnswer
Gold ⭐️
October 16, 2020

After I have not been able to find a solution that would be easy to implement inside of NiFi, I've written a small perl (yuk) script that can be used to adjust timestamps in a CSV file to be in ISO8601 format.  Maybe it is useful to someone else:

#!/bin/perl -w

# This perl script adds timezone information to timestamps without a
# timezone.  All timestamps in the input file that follow the format
# "YYYY-MM-DD HH:MM:SS" are converted to ISO8601 timestamps.

use strict;

use DateTime::Format::Strptime;

my $time_zone = 'Europe/Amsterdam';
my $parser = DateTime::Format::Strptime->new(
    pattern   => '%Y-%m-%d %T',
    time_zone => $time_zone
    );
my $printer = DateTime::Format::Strptime->new(
    pattern   => '%FT%T%z',
    time_zone => $time_zone
    );

while (<>) {
    s/(?<=")(\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d)(?=")/
        my $dt = $parser->parse_datetime($1);
        $printer->format_datetime($dt);
    /ge;
    print;
}

 

Translate
Terms & ConditionsAccessibility statement