AnsweredAssumed Answered

How do I convert a date with no offset, just literal time zone name?

Question asked by Marek Kaszycki on Dec 3, 2018
Latest reply on Dec 4, 2018 by Marek Kaszycki

How do I convert a date with no offset, just literal time zone name?

 

Hi,

 

I'm having issues converting dates that do not have the time offset listed, only a literal abbreviation of the time zone name.

Unfortunately, the time zone is not UTC or GMT. I have already covered those with this regular expression:

^\w{3},\s\d{1,2}\s\w{3}\s\d{4}\s\d{1,2}:\d{1,2}:\d{1,2}\s\w*$

and these scan patterns:

EEE, d MMM yyyy HH:mm:ss 'UTC'

EEE, d MMM yyyy HH:mm:ss 'GMT'

It works, though it's not as elegant as I'd like it to be (nevertheless, it's fine).

 

But suppose I have a date like this:

Sat, 24 Nov 2018 10:30:45 PST

 

The regular expression is be the same as above. But I'm stumped as to the scan pattern. Just to be clear, this doesn't work:

EEE, d MMM yyyy HH:mm:ss X

 

Using this:

EEE, d MMM yyyy HH:mm:ss 'PST'

yields this date:

2018-11-24T10:30:45Z (Wrong! Ignores the time zone entirely)

 

Is there any other directive in scan pattern that I should put in place of "X" to decode the literal time zone? I'm fine with ambiguity*, I just need a (relatively) correct date format for the index.

 

*) I understand that AST might be Alaska Standard Time, Atlantic Standard Time, Arabia Standard Time and some even use it for Australian (Eastern) Standard Time (nominally AEST).

 

I found this workaround works, but is very inelegant:

Before the "Date Conversion" step, add a "Replace" step that searches for the time zone abbreviation and replaces it with an offset. Like this:

Source Expression: PST

Replacement: -0800

 

The above date then becomes:

Sat, 24 Nov 2018 10:30:45 -0800

And this one is trivial to parse.

 

What I'm looking for is either a solution that already exists or RFE to incorporate this into a single Date Conversion step. This could be a bug, as so far it's very clear it's not possible to convert the date if it doesn't have the time offset listed.

 

Regards,

Marek

Outcomes