Issue with Date/Time Functions

I believe this confirms that NOW() and TIME() are both reporting unixtime in the local time zone:

###From Macro


Note that the time shown here is "18.57.06 GMT".
If I convert that to CST, then I subtract 6 hours: 12.57.06
which is exactly the same as the time from NOW()

Thus, NOW() is reporting the time in the local time zone.

@peternlewis, your thoughts?

Probably I’m missing something, but KM’s NOW() is reporting exactly the same Epoch Time as date +"%s" in the Terminal, at the moment 1482862092.

And this is GMT. (AFAIK there are no means to express local time in the Epoch Time format; it would have to output something like 1482865692+3600s to match the 1482862092 GMT seconds.)

Thanks for the feedback, @Tom.

How do you explain the confirmation I got with

Or, am I missing something?

NOW() always gives me Epoch Time (seconds since Jan 1, 1970, 00:00:00 GMT).

E.g. 1482778626

To get the local time I have to apply a formatter:

With KM this gives me the corresponding local time (assuming the computer has the correct time zone information):

==> 19:57:06 (= CET)

To get readable GMT time I have to force the time zone to GMT. I don’t know how to do this with KM. But in Swift for example you can add this to the formatter:

formatter.timeZone = NSTimeZone(name: "UTC") as TimeZone!
==> 18:57:06 (= GMT)

Or, the online converter you mentioned also gives me GMT, since apparently it doesn’t query my time zone.

But it’s all the same, no?

12:57:06 (CST) = 18:57:06 (GMT) = 19:57:06 (CET) = 1482778626 (seconds GMT)

1 Like

Well, if this is correct, it seems that all KM Functions that use NOW() are converting the results to Local Time:

DOW()  -- returns day number based on local time
DOW(NOW()) -- same as just DOW()

--- Returns Formatted Date/Time for Current Local time + 1 HR ---
%ICUDateTimeFor%NOW()+ (60*60) %EEE, MMM d, yyyy h:mm%

Maybe this section in the bottom of the Wiki page explains things:

==== Mixing Functions and Tokens ====

You might expect %ICUDateTimeFor% TIME(2017, 1, 1) %EEE, MMM d, yyyy% to yield Sun, Jan 1, 2017.
However, it may yield Sat, Dec 31, 2016, depending on your local time zone.

Here is why:

* The TIME() function takes and returns the values in GMT.
* The %ICUDateTimeFor% token displays the localized time.
* Thus, for TIME(Year, Month, Day) (which is in GMT), the ICUDateTimeFor displayed date in local time will be one less for anyone with a negative time zone offset.
* It is best is to use TIME(Year, Month, Day, 12, 0, 0) which will give noon GMT time, and thus the same day in local time for pretty much everyone in the world (everyone with time zone differences strictly between -12 hours and +12 hours).

So, if you use %ICUDateTimeFor% TIME(2017, 1, 1 , 12, 0, 0) %EEE, MMM d, yyyy% you will now get the expected result of Sun, Jan 1, 2017

###Key Statement:
The %ICUDateTimeFor% token displays the localized time

However, this is contidicted in other Wiki articles:
ICU Date/Time Tokens

%ICUDateTimeFor%NOW()+20%format% - the specified unixtime date.

“unixtime” implies GMT, but I now believe that the “ICUDateTimeFor” returns date/time in Local Time.

I just ran this test:

--- USE GMTOFFSET() TO ADJUST TIME(m,d,y,h,m,s) USE ---

NO OFFSET -- TIME(2016,12,27) ---
Mon, Dec 26, 2016 6:00 PM

USING OFFSET -- ICUDateTimeFor%TIME(2016,12,27) - (GMTOFFSET())%EEE, MMM d, yyyy h:mm a%
Tue, Dec 27, 2016 12:00 AM

So, instead of being approximately correct using:
%ICUDateTimeFor% TIME(2017, 1, 1 , 12, 0, 0) %EEE, MMM d, yyyy%

it seems better to me to just subtract the GMT Offset:
%ICUDateTimeFor%TIME(2016,12,27) - (GMTOFFSET())%EEE, MMM d, yyyy h:mm a%

###So, I think I’m coming around to this tentative conclusion:

  1. While NOW() and TIME() may return the current date/time in GMT, all of the KM Functions and Tokens that use them will automatically convert it to Local Time.
  2. The only time we need to adjust the time is when specifying a literal date/time using the TIME(y,m,d,h,m,s) syntax, and then we can just subtract GMTOFFSET() which will convert the time to GMT, and then the Function/Token converts it back to Local time.

@peternlewis, can you please confirm or correct this?

The NOW() and TIME() functions return the time in GMT.

The ICUDateTime tokens take a time in GMT and display the local time.

You could see this by, for example, looking at the value of the TIME() function and then changing your time zone setting. The NOW() function value would remain unchanged (well, increasing one second per second obviously). The resulting ICUDateTime would display a different time, the new local time for your new location.

This is the same, they are taking the GMT time (more accurately, the time in seconds since the 0:00, Jan 1, 1970 GMT) and returning the DOW for your local timezone.

this is referring to the input to the token. NOW()+20 is a unixtime. It is in the section at the top differentiating where the time is coming from in the four different ICUDateTime tokens (NOW() (GMT), a specified unixtime (GMT), NOW() plus or minus some offset).

That would probably be fine except on days with daylight saving switch, where GMTOFFSET() has changed since midnight - in one or other of those days, you would get the previous day just like you without any adjustment. Probably. Daylight Savings always makes my head hurt.


Relativistically-adjusted moving-point local times ?

(in-flight MacBook use and trips to Mars)

(The wiki could just refer users to a convenient (and translated) reference here: )

Ah, not really necessary. Time is not subject to relativity, since it moves faster than light:

If an event starts at 13:00 GMT, it means it starts at 7:00 CST, not at 7:00 + 0.02s.


Thanks for the clarification, Peter.
With your second statement, it now all makes perfect sense.

The second statement is not clearly stated in all of the relevant Wiki articles.

I shall endeavor to make some updates to the wiki to make this clear.

Interesting point. I just ran a test, and it seems that the token %ICUDateTimeFor% seems to account for DST:

  • For a date in June, it subtracted 5 hours
  • For a date in December, it subtracted 6 hours
--- USE GMTOFFSET() TO ADJUST TIME(m,d,y,h,m,s) USE ---

NO OFFSET -- TIME(2016,06,01) ---
Tue, May 31, 2016 7:00 PM

USING OFFSET -- ICUDateTimeFor%TIME(2016,06,01) - (GMTOFFSET())%EEE, MMM d, yyyy h:mm a%
Wed, Jun 1, 2016 1:00 AM

NO OFFSET -- TIME(2016,12,01) ---
Wed, Nov 30, 2016 6:00 PM

USING OFFSET -- ICUDateTimeFor%TIME(2016,12,01) - (GMTOFFSET())%EEE, MMM d, yyyy h:mm a%
Thu, Dec 1, 2016 12:00 AM

Thus, subtracting GMTOFFSET() from a date that is in DST but the current date is NOT in DST, will result in the date being 1 hour later.

In both cases, it ensures the adjusted date is the same day as what was entered in the TIME(y,m,d) function.

“Things you should know before delving into the mess of time…”

Yes, ICUDateTime et al take in to account DST.

The problem is that GMTOFFSET does not. So GMTOFFSET will give the GMTOFFSET at the current time. So the expression:

TIME(2016,06,01) - GMTOFFSET()

is ill-defined, because GMTOFFSET at 2016-06-01 might be a different value to the value at the current time.

So you could use:

TIME(2016,06,01) - GMTOFFSET() + 3*3600

which would always be early in the morning on the day in question regardless of any daylight savings changes as long as they were less than three hours which would always be the case.

Or you could just use


which will be right everywhere that is at most 12 hours from GMT which is practically everywhere always (exceptions appear to be parts of Kiribati, Samoa, maybe Fiji and Baker Island).

Any time I’m in Fiji I intend to be an the beach ignoring my Mac.

1 Like

Perhaps I’m missing something (likely), but it seems to me that this is NOT “ill-defined”, assuming that GMTOFFSET (for my time zone of CT) will always return:

  • 6 hours during non-DST
  • 5 hours during DST

So, in my above test, if run during DST, would correctly return:

USING OFFSET -- ICUDateTimeFor%TIME(2016,06,01) - (GMTOFFSET())%EEE, MMM d, yyyy h:mm a%
Wed, Jun 1, 2016 12:00 AM

So, during both DST and non-DST, this formula should always return the day indicated in the TIME(y,m,d) function:
ICUDateTimeFor%TIME(2016,06,01) - (GMTOFFSET())%EEE, MMM d, yyyy h:mm a%

Please advise if I am missing something.

I think Peter’s point was that, for those of us in the Central tine zone, GMTOFFSET will be 6 hours today (12/29/2016) even if the day you are trying to calculate is from June, when DST is in effect. But, maybe I’m the one who is missing something :slight_smile:

That is correct. But the result for a date in June is that the time will be off by only +1 HR, so the day is still correct. That is exactly what my original test showed:

USING OFFSET -- ICUDateTimeFor%TIME(2016,06,01) - (GMTOFFSET())%EEE, MMM d, yyyy h:mm a%
Wed, Jun 1, 2016 1:00 AM

I admit I haven’t been following this thread, and although I skimmed it, I probably missed some relevant things. That never stopped me before, though, from playing Cliff Clavin.

Awhile back I learned waaaaaay more than I ever wanted to know about DST. Here’s some things which you probably know, and perhaps some things you haven’t thought about:

  1. “Daylight Saving Time”. No plural. (I have no idea if anyone used plural here - I’m just stating a fact, and I’m not “correcting” anyone.)

  2. Obviously many countries don’t recognize DST. In the US, there’s some states, and even certain counties (not countries, counties) that don’t recognize DST.

  3. Countries that do support DST may or may not change on the same day.

  4. Here’s the one you probably haven’t thought about: Throughout the years, the specific day that DST starts and stops has changed. Think about this for a minute. In 2007, the US extended DST by a month. So this means if you honestly want to take DST into your calculations for elapsed hours through the years, it might be necessary to take this into account).

Bottom Line: I don’t believe you’ll find system-provided, or framework-provided elapsed date/time routines that take DST into account, except possibly to assume the rules that apply now have always applied, and that both date/times are in the same location as the computer doing the calculations.

I could be wrong. I totally admit that. However, my suggestion is to forget about DST in your calculations unless there’s a mission-critical reason to do that.


Calculate the elapsed time between March 20, 2006 13:00 and March 20, 2007 13:00. Got your answer? It’s probably wrong. March 20, 2006 was NOT DST, while March 20, 2007 WAS DST.

Having fun yet?

Dan, I appreciate your counsel, but I would like to clarify my use (or non-use) of DST.

In my above above formulas, the only time DST comes into play is when setting an explicit date/time using the TIME() function.

But I am NOT making any corrections specifically for DST, but simply showing that the formulas should work whether or not the current date is in DST, and whether or not the TIME() date is in DST.

Do you have an issue/concern with that?

None whatsoever. Just saw a bunch of stuff relating to DST, and had the overwhelming urge to spew knowledge and/or fallacy. Sometimes I just can’t help myself!

Nicely worded response, by the way. Very tactful. :stuck_out_tongue:

Sure, but what happens in the reverse case? Where it is June 1st and you are calculating the date for December 30.

In one case the effective time of TIME(Y,M,D)-GMTOFFSET() will be 1:00am and in the other it will be 11pm the previous day.

OK, that is a good point.
Does KM have a function/token that will identify when DST is in effect?

Here is perhaps one way:

Central Standard Time

According to the Formatting Dates and Times - ICU User Guide
"zzzz" produces “Pacific Daylight Time”

So we could test for “Daylight” in the current date, and add an extra hour to the formula.

OR, just always add an extra hour.

I guess this is now getting complicated enough that if all the user is interested in is the date, and not the time, then your suggestion of setting the date for noon is the simplest approach:

ICUDateTimeFor%TIME(2016,06,01, 12,0,0)%EEE, MMM d, yyyy h:mm a%
Wed, Jun 1, 2016 7:00 AM

It just feels like a kludge. :wink:
But sometimes a kludge is the best thing to do.

1 Like

No. I’m not sure if the system provides any such method. Really GMTOFFSET should itself take a time - but then technically it would have to know where you were at that time too.

Yes, that was my other suggestion, simply do:


which should give a safely small but positive time into the specified day in the local timezone.

Your call whether that is better or worse than