Working with Date and Time in Calculator

It may seem that the range of tasks that require manipulating date and time is not so large and not worth a detailed study. In addition, the Megaladata platform provides the user with a special Date and Time component, with the help of which you can extract the year, month, or other information from a field.

However, working with the Date/Time data type is not as trivial as it might seem. The Date and Time component is useful for solving simple problems or for facilitating the presentation of data as separate fields. When working with the Date/Time functions in Calculator, it becomes possible to build custom formulas.

Let's take a look at the logic of functions using specific examples.

Adding/subtracting time intervals

Functions of this group can change a Date/Time value by adding or subtracting a specified number of intervals.

For example, the function that adds/subtracts years is AddYear(Date, Number). There are several similar functions that operate other intervals — quarters, months, weeks, days, hours, minutes, seconds, and milliseconds. They are named respectively (AddQuarter, AddMonth, etc.).

Parameter Number of such functions can take positive or negative values. Let's look at an example when parameter Number is negative for the function AddDay. In this case, the function will return the earlier date by subtracting the specified number of days from the date in the field.

When working with Date/Time values that do not contain time information, the time in them is automatically set to 00:00:00.

Example: Let's use Calculator to determine the end date of a sprint. For a sprint lasting three weeks, you can use the AddWeek function. Write the expression as follows:

AddWeek(Date,3)

where the first argument, Date, is the start date, and the second argument, Number, is the number of weeks in the sprint. The function will return the end day of the sprint.

Converting Date/Time values to string type (and vice versa)

The following functions return a Date/Time value as a string:

• DateTimeToStr(Date_time [, DateFormat [, Time_format]]) will return the value of date and time (down to seconds) as a string.
• DateToStr(Date [, Format]) will return the information about the date only. There is also a function that performs a reverse operation, i.e. converts a string containing a date to a Date/Time value: StrToDate(Argument [, Format]).

The UnixToDateTime(UnixTime[, Return_In_UTC=True]) function converts a Unix timestamp string to a Date/Time type value. The ISO8601ToDate(Argument) function converts a string representing a date or time value in ISO 8601 format to a Date/Time value compatible with Megaladata. If the ISO 8601 string includes a time zone offset, the value is converted to the local time zone. Otherwise, the date and time is interpreted as UTC.

Example: Date and time formatting is useful for converting data to a universal text format correctly displayed in documentation. The inverse function allows you to convert string data to the Date/Time type suitable for loading into a database or performing mathematical operations.

Extracting date/time interval values

The function Year(Date) allows you to extract the data about the year from a Date/Time field. Similar functions for other intervals are: Month(Date), Week(Date), and Day(Date).

To retrieve the hour value from a given time, use the Hour(DateTime) function. Similarly, you can get the minutes or seconds using the Minute(DateTime) and Second(DateTime) functions.

Example: The Month(Date) function can be used to create an intermediate parameter for analyzing monthly sales.

Getting the day of the week

The DayOfWeek(Date) function returns the week day of the specified date under the ISO8601 standard. The days are denoted with integers from 1 to 7, where 1 is Monday.

Example: For precise wage calculation, employ the DayOfWeek(Date) function to determine the number of Saturdays and Sundays worked by an employee and aggregate the results accordingly.

Calculating the number of intervals between dates or time values

The following functions return the number of complete intervals of the specified type that passed between two date/time values. The result is an integer number of intervals that ended by the specified date or time.

The function that performs the operation with years is YearsBetween(Date1, Date2). There are similar functions for other time intervals: months, days, hours, minutes, seconds, and milliseconds.

Example: The MonthsBetween(Date1, Date2) function can be used in cohort analysis when evaluating the effectiveness of advertising. The function will retrieve the integer number of months that have passed during the selected time period.

Since Date/Time fields are stored as real numbers, it is possible to compute the difference between time points in the Calculator without using built-in functions. Here are some example expressions:

• (Date2 – Date1) × 24 — difference between two dates in hours.
• (Date2 – Date1) × (24 × 60) or (Date2 – Date1) × 1,440 — difference in minutes.
• (Date2 – Date1) × (24 × 60 × 60) or (Date2 – Date1) × 86,400 – difference in seconds.

However, such formulas complicate human understanding of calculations and require some comments (For example, explaining numbers: 86,400 is the number of seconds in a day, 28,800 stands for an eight-hour working day in seconds, etc.).

For an approximate calculation of the difference between dates in months and years, you could use the following formulas:

• (Date2 – Date1) / 30 — difference in months.
• (Date2 – Date1) / 365 – difference in years.

These expressions give an approximate estimate, since months can contain 28–31 days, and leap years are longer than regular. For exact calculations, use the built-in Calculator formulas.

Generating a date/time value

Megaladata has functions that allow you to generate a Date/Time field using the information about the year, month, and day stored in form of numbers. These operations are inverse to the extraction of time intervals:

• EncodeDate(Year, Month, Day).
• EncodeTime(Hours, Minutes, Seconds).
• EncodeDateTime(Year, Month, Day, Hours, Minutes, Seconds).

Getting information about current date/time

The functions Today() and Now() take no arguments and return the current date or date and time, respectively.

Example: Use Today() as an intermediate expression to calculate how many days have passed since a transaction was entered into a database.

Retrieving the start/end date of an interval

The functions of this group return the start/end date of a specified time range under the ISO 8601 standard.

One of the functions that return the start date of a period is StartOfTheYear(Date). There are similar functions for quarter, month, and week.

The functions that return the end date of a period are EndOfTheYear(Date) and similar (for quarter, month, and week).

Example: Employ the function StartOfTheWeek(Date) to retrieve the week start date for each sales day (use a Date/Time column as an argument). Then, use the new column to analyze weekly sales and make predictions.

Conclusion

To solve simple problems like retrieving time intervals from Date/Time fields, you can use the specialized Megaladata Date and Time component. For more complex operations, using Calculator functions is recommended. While many calculations can be performed using basic arithmetic, employing functions often provides a more intuitive and human-readable solution. These functions are consistent, concise, and their names accurately reflect the calculations they perform.

The Megaladata platform offers a wide range of options for working with Date/Time values without requiring programming languages: date/time arithmetic, comparison, formatting, extraction, and more. Problems that don't have a specific function can often be solved by combining a few existing ones. With Megaladata, date/time manipulation and analysis becomes a simpler and more valuable tool for data professionals.