Question

DateFormatter behaves oddly with seconds


Badge

I've got a duration in seconds that I want to format as hours and minutes so I thought I'd tell the DateFormatter that it was %s (seconds since the epoch) and ask for the output %k:%M.

The output is an hour too long. (FME 2016.1.3.1)

6861 comes out as 2:56

7861 comes out as 3:11

That's not right, is it?


20 replies

Userlevel 2
Badge +17

Hi @shepthedog63, where do you live? The epoch is 1970-01-01 00:00:00 UTC. I suspect that time zone setting in your machine is UTC+01 (Germany etc.).

Badge

Hi @shepthedog63

next week we are releasing FME 2017.0 - would you consider an upgrade? 

In FME 2017 date, time, and datetime functionality has been greatly improved. While the new DateTimeFormatter is coming in FME 2017.1, in 2017.0 you can use datetime functions to do formatting (and much more!) For your task, the expression would look like

@DateTimeFormat(@DateTimeParse(6861,%s),%H:%M)

where 6861 can be replaced with an attribute name or an expression.

To answer your question re an hour offset: @takashi is right, DateFormatter with %s as a format string attempts to do correction for your system time UTC offset as 

  • %s is always UTC time and 
  • your source value with no UTC offset is considered to be your system time. 

 

Badge

Hi @shepthedog63, where do you live? The epoch is 1970-01-01 00:00:00 UTC. I suspect that time zone setting in your machine is UTC+01 (Germany etc.).

Hi Takashi,

 

 

I'm in England, on UTC.

 

 

The clocks go forward one hour on the last Sunday in March, though, so I'm going to have to do the calculation by hand!

 

 

Thanks for the reply,

 

Ian

 

 

Badge

Hi @shepthedog63

next week we are releasing FME 2017.0 - would you consider an upgrade? 

In FME 2017 date, time, and datetime functionality has been greatly improved. While the new DateTimeFormatter is coming in FME 2017.1, in 2017.0 you can use datetime functions to do formatting (and much more!) For your task, the expression would look like

@DateTimeFormat(@DateTimeParse(6861,%s),%H:%M)

where 6861 can be replaced with an attribute name or an expression.

To answer your question re an hour offset: @takashi is right, DateFormatter with %s as a format string attempts to do correction for your system time UTC offset as 

  • %s is always UTC time and 
  • your source value with no UTC offset is considered to be your system time. 

 

Hi Lena,

 

 

I look forward to the upgrade but I don't think we'll get our Server instance 2017'ed in time for this piece of work.

 

 

Still a mystery where the extra hour comes from as I'm on the Prime Meridian.

 

 

Thanks,

 

Ian

 

 

Badge +10
Hi Lena,

 

 

I look forward to the upgrade but I don't think we'll get our Server instance 2017'ed in time for this piece of work.

 

 

Still a mystery where the extra hour comes from as I'm on the Prime Meridian.

 

 

Thanks,

 

Ian

 

 

Also on GMT and see exactly the same result here

 

 

Badge +10
Hi Lena,

 

 

I look forward to the upgrade but I don't think we'll get our Server instance 2017'ed in time for this piece of work.

 

 

Still a mystery where the extra hour comes from as I'm on the Prime Meridian.

 

 

Thanks,

 

Ian

 

 

Using the dateformatter on 0 gives the output 01/01/1970 01:00:00 so is that where the extra hour comes from?

 

 

Badge

Hi @shepthedog63

next week we are releasing FME 2017.0 - would you consider an upgrade? 

In FME 2017 date, time, and datetime functionality has been greatly improved. While the new DateTimeFormatter is coming in FME 2017.1, in 2017.0 you can use datetime functions to do formatting (and much more!) For your task, the expression would look like

@DateTimeFormat(@DateTimeParse(6861,%s),%H:%M)

where 6861 can be replaced with an attribute name or an expression.

To answer your question re an hour offset: @takashi is right, DateFormatter with %s as a format string attempts to do correction for your system time UTC offset as 

  • %s is always UTC time and 
  • your source value with no UTC offset is considered to be your system time. 

 

Hi Lena,

 

Testing on 2017 (Build 17245), if I have a DateFormatter with this setup:

 

Date Attributes: _creation_instance (attribute)
Source Date Format: %s
Destination Format: @DateTimeFormat(@DateTimeParse(0,%s),%H:%M)

 

The result is:

 

@DateTimeFormat(@DateTimeParse(0,0),01:00)

 

 

 

Which looks wrong in at least two ways (it hasn't parsed the functions, and the result has +1 hour (I'm UTC as well).

 

 

Even if I change the destination format to the ISO format, the output is:

 

1970-01-01T01:00:00

 

 

So this appears to still be misbehaving in 2017

 

Badge
I'd suggest reporting it to your support as a bug. A quick google seems to confirm that the epoch did indeed start at midnight UTC rather than 1am, so you shouldn't be getting +1 hour if you're in UTC.

 

 

Userlevel 2
Badge +17

That's really strange. In my time zone (UTC+09), this workflow returns expected result.

format-epoch-with-dateformatter.fmw (FME 2016.1.3)

 

0684Q00000ArL52QAF.png

Result (Time Zone: UTC+09)

Attribute(encoded: utf-8): `_epoch' has value `1970-01-01T09:00:00'

How does the workspace work in your time zone > everyone?

Badge +10

That's really strange. In my time zone (UTC+09), this workflow returns expected result.

format-epoch-with-dateformatter.fmw (FME 2016.1.3)

 

0684Q00000ArL52QAF.png

Result (Time Zone: UTC+09)

Attribute(encoded: utf-8): `_epoch' has value `1970-01-01T09:00:00'

How does the workspace work in your time zone > everyone?

Attribute(encoded: utf-8): `_epoch' has value `1970-01-01T01:00:00' 

 

Badge
Attribute(encoded: utf-8): `_epoch' has value `1970-01-01T01:00:00' 

 

Running takashi's workbench, I get the T01:00:00 as well despite my being UTC. That's running it in FME 2017.

 

 

Badge +16

That's really strange. In my time zone (UTC+09), this workflow returns expected result.

format-epoch-with-dateformatter.fmw (FME 2016.1.3)

 

0684Q00000ArL52QAF.png

Result (Time Zone: UTC+09)

Attribute(encoded: utf-8): `_epoch' has value `1970-01-01T09:00:00'

How does the workspace work in your time zone > everyone?

I'm at UTC+1 and the result is just fine (2017RC):

 

_epoch' has value `1970-01-01T01:00:00
Looks like an error occurs only at UTC
Badge
Hi Lena,

 

Testing on 2017 (Build 17245), if I have a DateFormatter with this setup:

 

Date Attributes: _creation_instance (attribute)
Source Date Format: %s
Destination Format: @DateTimeFormat(@DateTimeParse(0,%s),%H:%M)

 

The result is:

 

@DateTimeFormat(@DateTimeParse(0,0),01:00)

 

 

 

Which looks wrong in at least two ways (it hasn't parsed the functions, and the result has +1 hour (I'm UTC as well).

 

 

Even if I change the destination format to the ISO format, the output is:

 

1970-01-01T01:00:00

 

 

So this appears to still be misbehaving in 2017

 

Hi @jonathan

 

please don't use DateFormatter - this transformer (the library it is based on) is the root of the problem. We are going to deprecate DateFormatter in FME 2017.1 when we will have new DateTimeFormatter available. For now, please use all the new datetime functions in e.g. AttributeCreator. Sorry, I was not clear, I should have mentioned the do not use DateFormatter.

 

Badge
Hi Lena,

 

 

I look forward to the upgrade but I don't think we'll get our Server instance 2017'ed in time for this piece of work.

 

 

Still a mystery where the extra hour comes from as I'm on the Prime Meridian.

 

 

Thanks,

 

Ian

 

 

Hi Ian,

 

do you run your workspace on Server? What is the server time zone?

 

 

Badge
Using the dateformatter on 0 gives the output 01/01/1970 01:00:00 so is that where the extra hour comes from?

 

 

Hi @egomm

 

could you please check what your system time zone is? Do you use DateFormatter? Could you please try FME 2017 datetime functions?

 

We won't fix DateFormatter as we will deprecate it soon and replace with new DateTimeFormatter. The current DateFormatter has a list of known issues which we have no control over. I very much hope that the new FME 2017 datetime functions (and DateTimeFormatter in 2017.1) will produce correct results. If you see any issues with the new functions, please let us know ASAP - we are testing them now and any feedback will be very much appreciated.

 

Badge +10
Hi @egomm

 

could you please check what your system time zone is? Do you use DateFormatter? Could you please try FME 2017 datetime functions?

 

We won't fix DateFormatter as we will deprecate it soon and replace with new DateTimeFormatter. The current DateFormatter has a list of known issues which we have no control over. I very much hope that the new FME 2017 datetime functions (and DateTimeFormatter in 2017.1) will produce correct results. If you see any issues with the new functions, please let us know ASAP - we are testing them now and any feedback will be very much appreciated.

 

I don't have 2017 unfortunately.

 

 

But system date is definitely GMT - timestamper returns the correct time, as does using a date formatter on a attribute with value 'Now'

 

 

This particular issue is unlikely to cause problems for me day to day, I was just corroborating what @shepthedog63 had seen.

 

 

 

Badge
I don't have 2017 unfortunately.

 

 

But system date is definitely GMT - timestamper returns the correct time, as does using a date formatter on a attribute with value 'Now'

 

 

This particular issue is unlikely to cause problems for me day to day, I was just corroborating what @shepthedog63 had seen.

 

 

 

I understand. And appreciate your input.

 

Just to clarify: TimeStamper returns your system time without UTC offset (even if there is any). Although, to be honest, by now (based on everything you said) I doubt your system time is not GMT... I can't explain the DateFormatter behaviour without troubleshooting it in an environment that is as close to the one you are working in as possible. I know DateFormatter has some issues with 'assuming' the time zones - this is one of the reasons why we are deprecating it (the other option would be patching).

 

Userlevel 2
Badge +17

That's really strange. In my time zone (UTC+09), this workflow returns expected result.

format-epoch-with-dateformatter.fmw (FME 2016.1.3)

 

0684Q00000ArL52QAF.png

Result (Time Zone: UTC+09)

Attribute(encoded: utf-8): `_epoch' has value `1970-01-01T09:00:00'

How does the workspace work in your time zone > everyone?

Update. Once I changed timezone in the system to UTC, the workspace returned correct result.

 

Attribute(encoded: utf-8): `_epoch' has value `1970-01-01T00:00:00'
Windows 7 Japanese Edition

 

0684Q00000ArMgtQAF.png

Badge +1

Could _epoch work to compare dates in TESTER?? I tried your setting above, but it causes all my work to fall out the Failed port. I used DAteFormater for two fields set one less than the other. I also need is not NULL for both?? Thx

Userlevel 4

Could _epoch work to compare dates in TESTER?? I tried your setting above, but it causes all my work to fall out the Failed port. I used DAteFormater for two fields set one less than the other. I also need is not NULL for both?? Thx

It should work, the %s format is simply returning the number of seconds since the epoch (i.e. 01-01-1970), expressed as a decimal integer.

 

If you can't get it to work, consider posting it as a separate question with as much detail as possible.

Reply