Batch processing timefield

Viewing 16 reply threads
  • Author
    Posts
  • March 24, 2020 at 3:01 AM #39985

    Hans
    Participant

    I have a database with timefields. Already filled the database, but now I want to switch to another timezone. Is there a way I can sort of batch processing those fields and distract 6 hours from the current value?

    March 24, 2020 at 7:03 AM #39986

    Wolfgang
    Participant

    My first idea is, to make a calculation field (time+6) and say :
    time + 25.200
    “time” is my time field.
    Then “time+6” shows 6 hours later.

    I hope there will be a command, to COPY content of one field to another (batch). In this case you can copy time+6 to another field and can delete the two others.

    Also a good idea for programmers, to add timezones to time fields… I could create world clocks!

    March 25, 2020 at 11:32 AM #39999

    Brendan
    Keymaster

    Yes, this is one way to do it.

    Another would be to write a Form Script that loops through all of the records, reading the value from the field, subtracting the 6 hours and setting the value back again on the field and then calling form.saveAllChanges();

    March 25, 2020 at 4:56 PM #40012

    Sam Moffatt
    Participant

    If I recall correctly, Tap Forms will render the time in what ever time zone you are in presently so if you shift it by 6 hours and then change timezone then it will appear to be a different time again.

    It’d be great if there were at least the ability to save the time zone that was used when the date/time entry was created and then a second flag to control if the field displayed in the current time zone (e.g. it would be adjusted based on the users current time zone) or if the field always used the time zone of the field value. A third request would be to have the ability to default a time zone different from the users current time zone.

    It’d be cool if you could paste a date/time field on iOS. You can long tap a field to copy a value but you can’t paste that. If you have to enter a date long in the past then this is rather painful on iOS.

    March 26, 2020 at 4:06 AM #40024

    Hans
    Participant

    Thanks for your solutions. I’m not (yet) familiar with the scripting function so I tried the tip from Wolfgang first. Made a calculationfield and that gives the right time bij subtracting the 6 hours from the original timefield. Unfortunately the calculation fieldtype only has text, numeric and date layout, so there my plan gets stuck already.
    I think I’m going to put my question in the Script-forum.

    March 26, 2020 at 10:22 AM #40026

    Wolfgang
    Participant

    Here are 3 screenshots of my calculation…

    Attachments:
    You must be logged in to view attached files.
    March 27, 2020 at 1:30 AM #40048

    Hans
    Participant

    Ahhh… I see what I did wrong. At first I thought had to find the time-layout in the calculation field. Later I tried numeric (like you did) but that gave me no time but duration. A better look at the list I now saw there were two identical items, but with different layout results (see attachment). Guess this is a translation issue.
    So far so good, thanks Wolfgang… now heading for the next step, that is how to get the calculated value back to the original field OR a new field with that value which is no calculation field. I namely have to continu entering new time-values, but now in the new time.
    Progressing…. :-)

    Attachments:
    You must be logged in to view attached files.
    March 27, 2020 at 5:11 AM #40056

    Hans
    Participant

    Strange thing as well with the time-calculation is:
    If I add 6 hours to a time-field the outcome is +5 hours
    If I subtract 6 hours however the outcome is -7 hours?
    How can this be explained?

    March 27, 2020 at 8:50 AM #40057

    Sam Moffatt
    Participant

    Are these date/times on or near as a DST boundary?

    March 27, 2020 at 10:14 AM #40059

    Hans
    Participant

    That passed my mind yes Sam. If Tapforms corrects the time field according to the US DST than that can be the reason. I live in the Netherlands and we shift to DST this Sunday.

    March 27, 2020 at 6:01 PM #40061

    Sam Moffatt
    Participant

    I would expect it to be using local DST rules from tzinfo or similar so if that’s not your local timse then I guess that one is out. To be safe you could pick a point away from any of the DST cut overs to test with.

    March 29, 2020 at 2:06 AM #40086

    Hans
    Participant

    I waited untill today. We now are in DST too, so the CET (European) and CST (American) times both are in DST. When adding 6 hours to the excisting timefield results in +5 hours, while substracting results in minus 7 hours. I cannot understand what does cause this. See attachment the calculation and (in green boxes) the results in the fields.
    Any ideas and solutions are welcome.

    PS Wolfgang, in your first reply you advise:
    “My first idea is, to make a calculation field (time+6) and say :
    time + 25.200
    “time” is my time field.
    Then “time+6” shows 6 hours later.”
    Why did you add 7 hours (25.200) to get the result 6 hours later? Did you notice as well the adding needed an hour more? :-)

    Attachments:
    You must be logged in to view attached files.
    March 29, 2020 at 1:27 PM #40095

    Daniel Leu
    Participant

    It works well for me being in PDT. I even tried to set my computer to CEST and it reports the correct numbers.

    Just curious, could you create a script like follows and report what you get in the console? I hope I got the field names right.

    var tijdCET = record.getFieldValue(form.getFieldNamed('Tijd CET').getId());
    var tijdUS = record.getFieldValue(form.getFieldNamed('Tijd US').getId());
    
    console.log(JSON.stringify(tijdCET));
    console.log(tijdCET);
    
    console.log(JSON.stringify(tijdUS));
    console.log(tijdUS);
    
    Attachments:
    You must be logged in to view attached files.
    March 30, 2020 at 12:55 AM #40111

    Hans
    Participant

    Thanks a lot for thinking with me!

    This is the result of the script, hope I did the execution right (see attachment).

    30-03-20 09:52:59 / Trades / Tijdberekening
    “1970-01-01T14:51:02.000Z”
    Thu Jan 01 1970 15:51:02 GMT+0100 (CET)
    undefined
    undefined

    Attachments:
    You must be logged in to view attached files.
    March 30, 2020 at 9:32 AM #40118

    Daniel Leu
    Participant

    Please check this line:
    var tijdUS = record.getFieldValue(form.getFieldNamed('Tijd US').getId());
    I think I got the name ‘Tijd US’ wrong. Then the two undefined should go away.

    March 30, 2020 at 11:07 AM #40122

    Wolfgang
    Participant

    Hi Hans, yes I saw that +6 will add only 5 hours… so I change to 7 hours, heaven knows why!
    I changed the formula by myself to
    timeUS + (60*60*7)
    to see, that I calculate with 7…

    March 31, 2020 at 12:06 AM #40144

    Hans
    Participant

    Daniel thanks, I checked it but the names are right. The only difference is that “Tijd US” is a calculation Type field while “Tijd CET” is a Timefield Type.

    Haha… Wolfgang, so it stays a mistery :-)

Viewing 16 reply threads

You must be logged in to reply to this topic.