Is there a way to subtract hours from a timestamp field...???

[angelleye]

I've got a timestamp field in FM that a script is bringing in as GMT and it's 5 hours ahead of my actual time. Is there a way in FM to subract 5 hours from whatever is in that field so that it's correct for our time zone?

Any info would be greatly appreciated. Thanks!!

[Fenton]

A timestamp field is basically seconds, a lot of seconds. Look at one as GetAsNumber(timestamp field) to see.* So you'd subtract 5*3600.

*Format, Number.. "Leave data formatted as entered" to see as a regular number. It's currently: 63289723473, getting late -]

[angelleye]

Where do I add that subtraction? Forgive me...I'm new to FM. Developing for a client who uses it. I'm looking in the options for the field but I don't see anything that looks like what you're saying.

[LaRetta]

Another option - use a field (number) to hold the GMT so you can shift it by daylight savings etc. It can be global (if stored when standalone). Then create a calculation (result is timestamp) =

timestampField - GMT * 3600

If you don't want a GMT field, it would be:

timestampField - 5 * 3600

[angelleye]

ok, actually it looks like I figured it out for new records that get added...

Auctions::Auction_Timestamp_Start_Local - (5 * 3600)

and I unchecked the box that says "Do not replace existing value for field(if any)" but it's not changing all of the records that already have field populated.

How can I make that happen?

[LaRetta]

You can remove those parentheses - you don't need them because multiplication and division always comes before addition or subtraction - and multiplication applies only to the number immediately preceeding. So 3600 only multiplies (sees) the 5 without isolating it.

You can kickstart your auto-enter calculated standard timestamp field by changing the type to CALCULATION. When the calc dialog opens be sure to change below from number to timestamp. Do NOT make it unstored in the Storage. Say OK then OK to back clear out of field defintions. Then go back into define fields and change it from calculation back to standard timestamp field. Select Options and your calculation will still be there (under auto-enter tab). Back out of field definitions. This will force an update. BACK UP FIRST! :wink2:

[angelleye]

Cool. Thanks for the info!