offset date/time subsummary

[sean o mac]

Creating a subsummary by date is very easy and printing my records this way is a cinch. What I want to do is offset my 'day' and create a subsummary where the 'day' starts at 6 am and ends at 5:59 am so entries after midnight still print the same day before midnight. Any simple solutions to this one?

thanks

[comment]

Define a calculation field (result is Date) =

Timestamp ( EntryDate ; EntryTime - 21600 )

and use it as the breakfield for your sub-summary.

[sean o mac]

That is great, but can I assume the 21600 signifies 6 hours broken into seconds? This calculation you offered actually works to sort until 5 am not 5:59 am.

thanks

Edited June 13, 2009 by Guest

[comment]

can I assume the 21600 signifies 6 hours broken into seconds?

Of course.

This calculation you offered actually works to sort until 5 am not 5:59 am.

Hm. I think you may have discovered a bug. I'll have to look into this. Meanwhile, try:

Timestamp ( EntryDate ; EntryTime ) - 21600

[sean o mac]

no, its not a bug as far as I can see since the calculation itself works fine... I just put in 25200, which is 7 hours broken into seconds and that comes up with 6 am (not 5:59 which I would assume would just be 25140. Don't quite know why, but maybe you do?

[comment]

It looks like a bug, because while

Timestamp ( Date ( 1 ; 1 ; 2009 ) ; Time ( -1 ; 0 ; 0 ) )

returns "12/31/2008 23:00" as expected,

Timestamp ( Date ( 1 ; 1 ; 2009 ) ; Time ( 0 ; -30 ; 0 ) )

returns "1/1/2009 0:00" instead of the expected "12/31/2008 23:30".

It seems the problem is with negative times that have zero hours.

---

If someone on Windows could confirm this, I'd appreciate it.

Edited June 13, 2009 by Guest

[LaRetta]

I can confirm the incorrect result (and definite bug) on your second calculation on Windows, all versions back through 7.

[sean o mac]

Now I see what you are saying - and to take this one step further:

Timestamp ( Date ( 1 ; 1 ; 2009 ) ; Time ( 0 ; -60 ; 0 ) )

returns "12/31/2008 23:00" as expected,

Timestamp ( Date ( 1 ; 1 ; 2009 ) ; Time ( 0 ; -59 ; 0 ) )

returns "1/1/2009 0:00" instead of the expected "12/31/2008 23:59"

however...

Timestamp ( Date ( 1 ; 1 ; 2009 ) ; Time ( 0 ; -61 ; 0 ) )

returns "12/31/2008 10:59" as expected

So, maybe it is a bug after all.

What I am having a little trouble with understanding is how the calculation you originally offered assumes this 'missing' hour. But I guess that is the nature of a bug isn't it?

[comment]

Thanks - I guess I'll have to report this one too... :idot:

[comment]

What I am having a little trouble with understanding is how the calculation you originally offered assumes this 'missing' hour.

I'm not sure I understand your question. My original suggestion would have worked for all times before 6:00 AM, including those after 5:00 AM, but for this bug.

[sean o mac]

I'm not sure I understand your question. My original suggestion would have worked for all times before 6:00 AM, including those after 5:00 AM, but for this bug.

Apologies, I am confusing things here. I definitely understand that your original suggestion would have worked but for this bug. That is what I was saying to you actually, just poorly worded. Thanks for all your help.

[comment]

This has been now reported to FMI - along with some additional interesting discoveries:

http://forum-en.filemaker.com/fm/board/message?board.id=aut&thread.id=2638

I think this is a most serious bug, and you're all invited to add your weight to the complaint.

[LaRetta]

Well, you can sure find 'em! Thanks for reporting it. It is very surprising that this one has been around (undiscovered) for so long! :shocked2: