Skip to main content

EXAMPLE - NOW and TODAY Functions

This example illustrates you to generate the date and time values for the current date and timestamp in the specified time zone.

Functions:

Item

Description

NOW Function

Derives the timestamp for the current time in UTC time zone. You can specify a different time zone by optional parameter.

TODAY Function

Derives the value for the current date in UTC time zone. You can specify a different time zone by optional parameter.

DATEDIF Function

Calculates the difference between two valid date values for the specified units of measure.

Source:

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

FlightNumber

Gate

Arrival

1234

1

2/15/17 11:35

212

2

2/15/17 11:58

510

3

2/15/17 11:21

8401

4

2/15/17 12:08

99

5

2/16/17 12:12

116

6

2/16/17 13:32

876

7

2/15/17 16:43

9494

8

2/15/17 21:00

102

9

2/14/17 19:21

77

10

2/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:

ヒント

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

Parameter: Formula type

Single row formula

Parameter: Formula

NOW('America\/Los_Angeles')

Parameter: New column name

'currentTime'

Transformation Name

New formula

Parameter: Formula type

Single row formula

Parameter: Formula

TODAY('America\/Los_Angeles')

Parameter: New column name

'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:

注記

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

Parameter: Formula type

Single row formula

Parameter: Formula

DATEDIF(currentDate, Arrival, day)

Parameter: New column name

'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

Parameter: Condition

Custom formula

Parameter: Type of formula

Custom single

Parameter: Condition

today <> 0

Parameter: Action

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

Parameter: Formula type

Single row formula

Parameter: Formula

DATEDIF(currentTime, Arrival, minute)

Parameter: New column name

'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

Parameter: Columns

status

Parameter: Formula

if(status < -20, 'arrived', if(status > 20, 'scheduled', if(status <= 0, 'landed', 'arriving')))

Results:

You now have a daily flight status report:

currentDate

currentTime

FlightNumber

Gate

Arrival

status

today

2017-02-15

2017-02-15·11:46:12

1234

1

2/15/17 11:35

landed

0

2017-02-15

2017-02-15·11:46:12

212

2

2/15/17 11:58

arriving

0

2017-02-15

2017-02-15·11:46:12

510

3

2/15/17 11:21

arrived

0

2017-02-15

2017-02-15·11:46:12

8401

4

2/15/17 12:08

scheduled

0

2017-02-15

2017-02-15·11:46:12

876

7

2/15/17 16:43

scheduled

0

2017-02-15

2017-02-15·11:46:12

9494

8

2/15/17 21:00

scheduled

0

2017-02-15

2017-02-15·11:46:12

102

9

2/14/17 19:21

arrived

0

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