# EXAMPLE - NOW and TODAY Functions

This example illustrates how the `NOW` and `TODAY` functions operate. Both functions generate outputs of Datetime data type.

• `NOW` - Generates valid Datetime values for the current timestamp in the specified time zone. See NOW Function.
• `TODAY` - Generates valid Datetime for the current date in the specified time zone. See TODAY Function.
• `DATEDIF` - Calculates the difference between two Datetime values based on a specific unit of measure. See DATEDIF Function.

Source:

The following table includes flight arrival information for Los Angeles International airport.

FlightNumberGateArrival
123412/15/17 11:35
21222/15/17 11:58
51032/15/17 11:21
840142/15/17 12:08
9952/16/17 12:12
11662/16/17 13:32
87672/15/17 16:43
949482/15/17 21:00
10292/14/17 19:21
77102/16/17 12:31

Transformation:

You are interested in generating a status report on today's flights. To assist, you must generate columns with the current date and time values:

Tip: You should create separate columns containing static values for `NOW` and `TODAY` functions. Avoid creating multiple instances of each function in your dataset, as the values calculated in them can vary at execution time.

Transformation Name `New formula` `Single row formula` `NOW('America\/Los_Angeles')` `'currentTime'`

Transformation Name `New formula` `Single row formula` `TODAY('America\/Los_Angeles')` `'currentDate'`

Next, you want to identify the flights that are landing today. In this case, you can use the `DATEDIF` function to determine if the `Arrival` value matches the `currentTime` value within one day:

NOTE: The `DATEDIF` function computes difference based on the difference from the first date to the second date based on the unit of measure. So, a timestamp that is 23 hours difference from the base timestamp can be within the same unit of day, even though the dates may be different (`2/15/2017` vs. `2/14/2017`).

Transformation Name `New formula` `Single row formula` `DATEDIF(currentDate, Arrival, day)` `'today'`

Since you are focusing on today only, you can remove all of the rows that do not apply to today:

Transformation Name `Filter rows` `Custom formula` `Custom single` `today <> 0` `Delete matching rows`

Now focusing on today's dates, you can calculate the difference between the current time and the arrival time by the minute:

Transformation Name `New formula` `Single row formula` `DATEDIF(currentTime, Arrival, minute)` `'status'`

Using the numeric values in the `status` column, you can compose the following transform, which identifies status of each flight:

Transformation Name `Edit column with formula` `status` `if(status < -20, 'arrived', if(status > 20, 'scheduled', if(status <= 0, 'landed', 'arriving')))`

Results:

You now have a daily flight status report:

currentDatecurrentTimeFlightNumberGateArrivalstatustoday
2017-02-152017-02-15·11:46:12123412/15/17 11:35landed0
2017-02-152017-02-15·11:46:1221222/15/17 11:58arriving0
2017-02-152017-02-15·11:46:1251032/15/17 11:21arrived0
2017-02-152017-02-15·11:46:12840142/15/17 12:08scheduled0
2017-02-152017-02-15·11:46:1287672/15/17 16:43scheduled0
2017-02-152017-02-15·11:46:12949482/15/17 21:00scheduled0
2017-02-152017-02-15·11:46:1210292/14/17 19:21arrived0

The `currentDate`, `currentTime`, and `today` columns can be deleted.