Submissions: Working With Date And Time Values

The Challenge

All date and time values in doForms are stored in the Universal Time (UTC) format which is also known as Greenwich Mean Time (GMT). This is a standard approach that allows for the precise recording of any moment in time, regardless of which time zone the user was in when they recorded the time.

Most businesses, of course, need to see time information in the local time zone so the best practice for applications is to store date and time values in UTC and only convert them to local time before display.

The doForms web site follows this practice and allows you to set a time zone under Account and then Time Zone Settings:

Note that this only changes the way time is displayed on the web site. All time values are still stored in UTC.

If your system / application follows this practice, then no changes to the doForms data will be necessary. If however, your application requires local time, then some additional work is required...

Time Zones and Offset

To convert from UTC time to a local time zone, you first need to know how many hours or minutes to add or subtract from the UTC time. This is commonly called an Offset and is often specified as part of a time zone description, e.g. "Central Time (U.S. and Canada) GMT-6:00". The GMT-6:00 indicates that you subtract 6 hours from GMT/UTC to convert to the Eastern time zone. If you are unsure what your current time zone offset is, try taking a look at this map of the current time around the world.

Note that the offset for a time zone will change during Daylight Savings Time. For instance, the offset for Central Time (U.S. and Canada) changes from -6:00 to -5:00 during DST.

Each time a form is completed on a device, doForms captures the current time zone offset (in minutes) of the device and stores it in a field called timezone:

Converting To Local Time

  • Convert all submission times to the device time

    To convert all submission time values to the device timezone, select the "Convert submission times to local" under Tools | Options | Jobs | Submission.

  • Use the device timezone offset

    To convert individual date/time or time fields to the local time of the device, select the Local Date Time data type:



    If the "Convert submission times to local" option is selected then this configuration does not change the time value.

  • Specify a timezone offset

    To convert individual date/time or time fields to a timezone other than the device, choose the Local Date Time data type and enter the field and offset separated by a coma:



    This will convert all values to the same time zone by adding -300 minutes. Note that the Name of the field should be entered in the Name column.

    This configuration will override the "Convert submission times to local" option.
  • Use a special field

    The special fields Date_Created_Local and Date_Received_Local contain the local (device specific) values of the Date_Created and Date_Received fields.

Note that using the timezone offset captured from the device will convert each time value to the time zone of the individual user. If you have users in multiple time zones this can lead to confusion. For instance, consider the example of a user named Jim in the Eastern (U.S. and Canada) time zone who enters a time of 10:00 AM and a user named Bob in the Central (U.S. and Canda) time zone who enters a time of 9:05 AM. Later, when someone views this data in another application, they might suppose that the event entered by Jim at 10:00 AM occurred after the event entered by Bob at 9:05 AM, when the reverse is actually true.

Converting After Export


To use your back end system (instead of Sync & Save) to convert to local time, you will need both the timezone offset and your time value(s). In the example below, the Time_question and timezone values are exported to two separate fields:



Once you have the Offset value for a submission, you will need to convert each time value to the correct local time. Below are examples for some output options:

Excel

If you have a UTC time value in A1 and the offset (in minutes) in B1, the formula in C1 will convert to local time. First we have to convert the time to a date (we choose 1/1/2000 arbitrarily) and then we add the number of days represented by the offset (minutes divided by number of minutes in 1 day) to convert to local time.

If you have a UTC date and time value, then the conversion to a date is not necessary and the equation would just be A1 + B1/1400.

Access

If you have a UTC time or date value in a Date/Time field in a table called "s" the following query expression will convert it to local time:

LocalTime: DateAdd("n",[s].[timezone],[Time_question])

Note that if you are converting a time value, this will display with a date value as well (e.g. 7/8/2013 9:30:40 PM) because the Access field is a Date/Time format. The date portion is not the date that the time was entered, instead it is the date the record was inserted into Access. If you want to eliminate this date portion, use the following query expression:

LocalTime: Format(DateAdd("n",[s].[timezone],[Time_question]),"hh:nn:ss ampm")

Sql Server


If you have a UTC time or date value you can convert to a local time using the query expression above.

Note that the if you just have a time value, the date portion of the value comes from the default of Jan 1 1900. If you want to only display the time portion of the value, use the following expression:

SELECT SUBSTRING(CONVERT(varchar, DATEADD(N, timezone, Time_question), 0), 12, 8) AS LocalTime FROM t

Result: "9:30PM"

Or if you prefer the 24 hour clock, use:

SELECT
CONVERT(varchar, DATEADD(N, timezone, Time_question), 0) AS LocalTime FROM t

Result: "21:30:40:000"

Walkthrough

  1. Introduction
  2. System Requirements
  3. Main Screen
  4. Submissions: Adding A New Job
  5. Submissions: Configuring the Export Settings
  6. Submissions: Setting Up the Export Format
  7. Submissions: Using The Submissions Tab
  8. Submissions: Dealing With Errors
  9. Submissions: Handling Repeating Data
  10. Submissions: Exporting To Text Files
  11. Submissions: Exporting To Sql Server, MySQL, Oracle, Access, Sybase ASE, or ODBC
  12. Submissions: Exporting To Excel
  13. Submissions: Exporting To PowerPivot For Excel
  14. Submissions: Exporting to Word
  15. Submissions: Exporting To QuickBooks
  16. Submissions: Exporting To QuickBooks Customer
  17. Submissions: Exporting To QuickBooks Estimate, Sales Order, Invoice, Sales Receipt
  18. Submissions: Exporting To QuickBooks Vendor
  19. Submissions: Exporting To QuickBooks Purchase Order
  20. Submissions: Exporting To QuickBooks Item Receipt
  21. Submissions: Exporting To QuickBooks Bill
  22. Submissions: Exporting to QuickBooks Receive Payment
  23. Submissions: Exporting To QuickBooks Time Entry
  24. Submissions: Exporting To QuickBooks Online
  25. Submissions: Exporting To QuickBooks Online Customer
  26. Submissions: Exporting To QuickBooks Online Estimate, Invoice, Sales Receipt
  27. Submissions: Exporting To QuickBooks Online Vendor
  28. Submissions: Exporting To QuickBooks Online Purchase Order
  29. Submissions: Exporting To QuickBooks Online Bill
  30. Submissions: Exporting to QuickBooks Online Time Activity
  31. Submissions: Exporting To Sage 50 (Peachtree)
  32. Submissions: Exporting To Sage 50 Quote, Sales Order, Sales Invoice
  33. Submissions: Exporting To Sage 50 Customer
  34. Submissions: Exporting To Sage 100 (MAS 90 / MAS200)
  35. Submissions: Exporting To Sage 100 Sales Order, SO Invoice, AR Invoice
  36. Submissions: Exporting To SharePoint
  37. Submissions: Exporting To Salesforce.com
  38. Submissions: Exporting To Evernote
  39. Submissions: Exporting Attachments
  40. Submissions: Exporting PDF Report
  41. Submissions: Exporting A Specific Date Range
  42. Submissions: Filtering Submissions
  43. Submissions: Archive
  44. Submissions: Approve Before Exporting
  45. Submissions: Working With Date And Time Values
  46. Submissions: Working With Select One and Select Multiple fields
  47. Submissions: System Fields
  48. Submissions: Handling New Versions Of A Form
  49. Submissions: Networkfleet
  50. Submissions: Fleetmatics
  51. Lookup Table: Adding A New Job
  52. Lookup Table: Settings
  53. Lookup Table: From A Sql Source
  54. Lookup Table: From A File Source
  55. Lookup Table: From QuickBooks
  56. Lookup Table: From QuickBooks Online
  57. Lookup Table: From Sage 50
  58. Lookup Table: From SalesForce.com
  59. Lookup Table: From A SharePoint List
  60. Lookup Table: From NetworkFleet
  61. Lookup Table: From Fleetmatics
  62. Lookup Table: Filtering Records
  63. Dispatch: Adding A New Job
  64. Dispatch: Settings
  65. Dispatch: Format
  66. Dispatch: From An SQL Source
  67. Dispatch: From QuickBooks
  68. Dispatch: Tables And Repeating Sections From An SQL Source
  69. Dispatch: From A File Source
  70. Dispatch: Tables And Repeating Sections From An XML Source
  71. Dispatch: From Salesforce.com
  72. GPS: Adding A New Job
  73. GPS: Configuration
  74. Options: Calculations
  75. Options: Encryption
  76. Options: Proxy Server
  77. Options: Email Notifications
  78. Connecting to Sharepoint.com
  79. Connecting to Sharepoint
  80. Connecting To Cloud File Systems
  81. Connecting To SFTP
  82. Connecting To QuickBooks
  83. Connecting To QuickBooks: User Account
  84. Calculating tax on a form for QuickBooks
  85. Scheduling Jobs
  86. Job Log
  87. Run Jobs From An External Application
  88. Sending Information To Support
  89. Sending Job Configuration to Support
  90. Licensing
  91. Upgrading
  92. Backing Up And Restoring
  93. Webservice Quota
  94. Release Notes
  95. Release Notes 1.15.x
  96. Release Notes 1.14.x
  97. Release Notes 1.13.x
  98. Release Notes 1.12.x
  99. Release Notes 1.11.x
  100. Third Party Software

Feedback and Knowledge Base