Skuid and Date/Time

Skuid can interact with a variety of data sources that utilize date fields (which represent a calendar day) and date/time fields (which represent a calendar day and specific time).

Note

Date/time is also commonly written as datetime.

Server and device time zones

When working with date/time in applications built with Skuid, it’s important to consider that Skuid almost always prefers server time zone to device time zone.

Server time zone refers to a specific time zone stored on the environment server, whether that be an environment-wide default that applies to all users or a unique, user-chosen time zone.

In Skuid SFX, the server time zone is determined by org-default time zone settings, unless a user updates their Salesforce personal settings to a different time zone.

For information about org-default time zones, see Salesforce’s Language, Locale, and Currency Settings topic

For information about user’s personal settings, see Salesforce’s My Settings documentation

Device time zone refers to, as the name implies, the time zone setting on the end user’s current device. Because devices can navigate between time zones (or the time zone may differ from the Skuid environment’s default or the user’s server time zone), it is not often used in Skuid. The deprecated TODAY formula function is a rare exception.

Formatting conventions and time zone rendering

Skuid’s date and date/time strings are always expressed using Coordinated Universal Time (UTC)—a common time standard in computing. By utilizing UTC, Skuid ensures that these values are accurate even when viewed in other time zones.

Data and date/time fields also follow ISO 8601 formatting conventions:

  • Date: yyyy-MM-dd
  • Date/time: yyyy-MM-dd'T'HH:mm:ss[.SSS]'Z'

(The date formats above conform to the Java Simple Date Format)

The milliseconds portion of the date/time is reset to “.000” when the value is stored in the database, regardless of the time. For this reason, the millisecond portion of the string will always be included when the string is generated by Skuid, but will be ignored if provided as an input.

All date/time strings are assumed to be using UTC (“+0000”), denoted by the Z at the end of the timestamp. This has two important ramifications:

  • All date/time values created or modified in Skuid are saved in the UTC time zone, regardless of the user’s server time zone. Any server time zone preference (site-wide or user-selected) will be ignored.
  • However, at runtime Skuid converts date/time field values from UTC to the user’s server time to display a user-friendly date/time format.

This means a Skuid end user may see a friendly date/time at runtime that looks different than the full timestamp stored in the data source, but that’s only because Skuid is performing the conversion at runtime.

As an example, consider an end user with a server time zone of America/New_York (GMT-4:00) at exactly 4 PM local time on October 20th 2022. They use Skuid’s NOW() function to save a value to a record. The following occurs:

  • Skuid saves a record with a raw value of 2022-10-20T19:00:00.000Z

    (Note the Z, denoting the value is in UTC)

  • Skuid renders the data in the user’s time zone as 10/20/2022 4:00 pm

Date/time features in Skuid

Time keywords for model conditions [[]]

The following keywords can be used in model conditions on date/time fields.

Note

For number-based keywords (indicated by a capital N), append :<number> to define the number value.

For example, to create a value of “10 years ago,” use N_YEARS_AGO:10, which would cover the range: 1/1/2012 >= [date_field] < 1/1/2013). These keywords can only accept positive numbers.

  • NOW
  • YESTERDAY
  • TODAY
  • TOMORROW
  • LAST_90_DAYS
  • NEXT_90_DAYS
  • LAST_N_DAYS:<number>
  • NEXT_N_DAYS:<number>
  • N_DAYS_AGO:<Number>
  • LAST_WEEK
  • THIS_WEEK
  • NEXT_WEEK
  • LAST_N_WEEKS:<number>
  • NEXT_N_WEEKS:<number>
  • N_WEEKS_AGO:<number>
  • LAST_MONTH
  • THIS_MONTH
  • NEXT_MONTH
  • LAST_N_MONTHS:<number>
  • NEXT_N_MONTHS:<number>
  • N_MONTHS_AGO:<number>
  • LAST_QUARTER
  • THIS_QUARTER
  • NEXT_QUARTER
  • LAST_N_QUARTERS:<number>
  • NEXT_N_QUARTERS:<number>
  • N_QUARTERS_AGO:<number>
  • LAST_YEAR
  • THIS_YEAR
  • NEXT_YEAR
  • LAST_N_YEARS:<number>
  • NEXT_N_YEARS:<number>
  • N_YEARS_AGO:<number>

Note

Strings used to set a date or datetime value for future dates, such as NEXT_N_DAYS or NEXT_N_WEEKS, return a range of dates that match the query. Because these functions return a range of values—not a single value—only the first value will be used.

For example: If you set NEXT_N_DAYS to 5, it will return a range consisting of Next Day 1 - Day 5. Skuid will use the Day 1 value since it is the first value in that range.

Skuid date / time formula functions [[]]

Skuid’s formula functions are used in a variety of data-related features, particularly in updating row values using the result of formulas or in UI-only fields

Listed below are all time-related functions. To learn more about formula functions in general, see the Formula and Function Reference.

Note

While there is some overlap between formula functions and time keywords (e.g. NOW), they are not intrinsically linked. Be sure to reference the appropriate reference.

  • AMPM: Adds “am” or “pm” to a datetime field.

  • DATE_ADD: Takes a source date value, adds a specified amount of time to it—for example, 15 minutes, 2 weeks, or 5 months—and returns a new date. (Use a negative integer to subtract time.)

    Example:

    • DATE_ADD(Date value, number, unit string, such as SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR).

    Example:

    • DATE_ADD(MyDate, 1, WEEK)
  • DATE_DIFF: Calculates the difference between two dates to determine how much time exists between them, then returns an amount of time in the designated time span (minutes, days, weeks, etc.)

    The syntax is DATE_DIFF(first Date value, second Date value, unit string, such as SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR). If a “first Date value” isn’t designated, the default for that value is NOW.

    Example:

    • DATE_DIFF(MyDate, MySecondDate, DAY)
  • DAY_NAME_SHORT: Returns an abbreviated name for the day of the week from a Date or datetime Field as an internationalized label.

  • DAY_NAME: Returns the full name for the day of the week from a Date or datetime field as an internationalized label.

  • DAY_OF_WEEK_UTC: Returns a number corresponding to the UTC (Universal Coordinated Time) code for the day of the week from a Date or datetime field.

  • DAY_OF_WEEK: Returns a number corresponding with the day of the week (in local time) from a Date or datetime field.

  • DAY: Returns a number corresponding with the day in a month from a Date or datetime field.

  • FORMAT_DATE: Returns a string with the date formatted according to the format string specified. The format string follows jQuery’s $.datepicker.formatDate(), whose format characters are based on Java’s SimpleDateFormat. For more technical information, see the skuid.time topic.

  • FORMAT_TIME: Returns a string with the time portion of a datetime field formatted according to the format string specified. The format string follows jQuery’s $.datepicker.formatDate(), whose format characters are based on Java’s SimpleDateFormat. For more technical information, see the skuid.time topic.

  • HOUR_UTC: Returns the hour portion of a datetime field with no time zone adjustment.

  • HOUR: Returns the hour portion of a datetime field, adjusted for the time zone of the system.

  • MIDNIGHT: Returns the datetime value for midnight in the user’s server time zone by default, but accepts two optional parameters that can change this behavior:

    1
    MIDNIGHT(dateValue, timeZone)
    
    • dateValue: Determines a date other than the current date returned by NOW() to be used when retrieving the midnight value
    • timeZone: Determines a time zone other than the user’s server time zone to be used when retrieving the midnight value

    If you want to specify just the time zone while retrieving midnight for the current date, use in conjunction with NOW():

    1
    MIDNIGHT(NOW(), {{Customer_TimeZone}}),
    

    To retrieve midnight for a different date, provide only the dateValue parameter. For example, to retrieve the midnight value for tomorrow:

    1
    MIDNIGHT(DATE_ADD(NOW(), 1, DAY))
    

    Note

    As with all Skuid date/time fields, this function renders in the user’s time zone but saves in the UTC time zone. See the Formatting conventions and time zone rendering section for more information.

  • MILLISECOND: Returns the millisecond portion of a datetime field (if there is one.)

    Note

    Salesforce Dates do not contain milliseconds. This function is only relevant for data sources other than Salesforce.

  • MINUTE_UTC: Returns the minute portion of a datetime field with no time zone adjustment.

  • MINUTE: Returns the minute portion of a datetime field, adjusted for the time zone of the system.

  • MONTH_NAME_SHORT: Returns an abbreviated name of the month from a Date or datetime field as an internationalized label.

  • MONTH_NAME: Returns the full name of the month from a Date or datetime field as an internationalized label.

  • MONTH_UTC: Returns the number corresponding to the UTC (Universal Coordinated Time) code for the month from a Date or datetime field with no time zone adjustment.

  • MONTH: Returns the number for the month from a Date or datetime field, adjusted for the time zone of the system.

  • NOW: Returns a datetime that represents now.

    Note

    As with all Skuid date/time fields, this function renders in the user’s time zone but saves in the UTC time zone. See https://docs.skuid.com/latest/v2/en/the Formatting conventions and time zone rendering section for more information <skuid/date-time/#formatting-conventions-and-time-zone-rendering>.

  • SECOND_UTC: Returns the second portion of a Datetime field corresponding to the UTC (Universal Coordinated Time), with no time zone adjustment.

  • SECOND: Returns the second portion of a Datetime field, adjusted for the time zone of the system.

  • TIMESTAMP(Date/datetime value): Returns the number of milliseconds that have passed between the given date and the Unix epoch.

    This formula function requires a date or datetime value as an argument. These date/datetime values can be retrieved from other formula functions or field values through the use of merge variables.

    For example, to get a timestamp for the current moment at runtime:

    1
    TIMESTAMP(NOW())
    

    To get a timestamp for a datetime field with the ID “CreatedDate”:

    1
     TIMESTAMP({{CreatedDate}})
    
  • YEAR_UTC: Returns the number corresponding to the UTC (Universal Coordinated Time) for the year from a Date or datetime field with no time zone adjustment.

  • YEAR: Returns the number for the year from a Date or datetime field, adjusted for the time zone of the system.

Salesforce date functions [[]]

Skuid models on Salesforce objects with date/time fields use Salesforce’s own date function logic in areas like model conditions.

Note

While many of Salesforce date functions are similar to Skuid’s own functions, they are technically not the same logic. Salesforce date functions are run by Salesforce, while Skuid’s functions are run by Skuid.

For example, Salesforce’s DAY_IN_WEEK and Skuid’s DAY_OF_WEEK have the same purpose, but Salesforce functions can only be used for Salesforce conditions; similarly, Skuid functions can be used with any other data source.

The Calendar component and its merge variables [[]]

The Calendar component is used to display events and tasks from one or more date/time sources on a Skuid page. There are several merge variables available for use within it.

Calendar merge variables allow access to the date/datetime of the selected area—which can be an empty time slot or an existing event:

  • startDateTime and endDateTime : Return the values for the selected area’s start datetime or end datetime, respectively.

    For example, on an event space that represents 5/4/2021 10:00 am to 5/4/2021 10:30 am Eastern Standard Time, the merge variable template {{startDateTime}} - {{endDateTime}} would do the following:

    • Display 5/4/2021 10:00 am - 5/7/2021 10:30 am at runtime
    • Set a field value to 2021-05-04T14:30:00.000Z - 2021-05-07T15:00:00.000Z

    These merge variables can be used in:

    • On-click actions
    • Create event actions
    • Event update actions
  • startDate and endDate : Return the values for the selected area’s start date or end date, respectively.

    For example, on an event space that represents 5/4/2021 10:00 am to 5/7/2021 10:30 am Eastern Standard Time, the merge variable template {{startDate}} - {{endDate}} would do the following:

    • Display 5/4/2021 - 5/7/2021 at runtime
    • Set a field value to 2021-05-04 - 2021-05-07

    These merge variables can be used in Create event actions.

  • allDay: Returns true if the selected area/event takes up the full day.

    • On-click actions
    • Create event actions
    • Event update actions

skuid.time API [[]]

For more complex use cases that involve JavaScript snippets, the skuid.time API provides several useful functions in terms of formatting dates, parsing dates, and dealing with commonly used time keywords.

For more information, see the skuid.time reference.