Why Does EpochToDate Exist?

1

Maybe it’s just me, but it seems like calling ToNumber on something that is an actual Date rather than something that looks like a date should return the epoch value that represents that date.

For example, EpochToDate(ToDate("1-1-1970").ToNumber()).ToDate() should return 1/1/1970. It actually returns 12/31/1969. Weird.

But wait, it gets worse.

// Going forward in time twenty years...
EpochToDate(ToDate("1-1-1990").ToNumber()).ToDate()
    // ^ should return something close to right?
    // It actually returns 1/1/1970
    // This happens with any reasonable date value

// If we pull the logic outside of the EpochToDate method...
ToDate("1-1-1990").ToNumber()
    // ^  Only returns about 33,000

// If we add in an adjustment for seconds...
EpochToDate(ToDate("1-1-1990").ToNumber() * 24 * 60 * 60).ToDate()
    // ^ We somehow get 1/2/2060.  What?

// Oh, maybe we need to subtract the epoch zero value from the value?
EpochToDate(ToDate("1-1-1990").ToNumber() * 24 * 60 * 60 - ToDate("1-1-1970").ToNumber() * 24 * 60 * 60)
    // ^ Oh!  12/31/1989! So close!

It turns out that in order to get a value that can be evaluated by the EpochToDate method, you have to use the following:

(ToDate("1-1-1990").ToNumber() + 1) * 24 * 60 * 60 - ToDate("1-1-1970").ToNumber() * 24 * 60 * 60

After doing some digging, it turns out that [Date].ToNumber() yields the number of days since 1/1/1900, which is 70 years earlier than the documented date in the EpochToDate documentation and uses a totally different unit…

Given that that’s what’s required and there is no method DateToEpoch, does anyone know (or have a guess) why EpochToDate even exists? Is there something that I’m just totally not seeing?

2 Likes
2

I was looking for a Time.ToEpoch when I came across your post. Just replying because I’d be incredibly frustrated if I had gone down the rabbit hole you just went through to try to figure out why my date formulas weren’t working as expected.

2 Likes