Date and time functions | IBM Cloud Pak for Data as a Service

Date and time functions

You can use the date and time functions to perform various operations on dates and times in the Transformer stage.

Functions that specify dates, times, or time stamps in the arguments use strings with specific formats:

For a date, the format is %yyyy-%mm-%dd
For a time, the format is %hh:%nn:%ss. If extended to include microseconds, the format is %hh:%nn:%ss.x where x gives the number of decimal places seconds is given to.
For a time stamp, the format is the date format followed by the time format.

Functions that have the days of week in the argument take a string that specifies the day of the week. The day is specified as a three-letter abbreviation, or the full name. For example, the strings "thu" and "thursday" are both valid.

The following functions are in the Date & Time category of the expression editor. Square brackets indicate that an argument is optional. The examples show the function as it appears in a Derivation field in the Transformer stage.

CurrentDate

Returns the date that the job runs.

Input: Not applicable
Output: date (date)
Examples. Use this function to add a new column that contains the date to the output data by the Transformer stage.
```
CurrentDate()
```

CurrentTime

Returns the time at which the job runs.

Input: Not applicable
Output: time (time)
Examples. Use this function to add a new column that contains the time to the output data by the Transformer stage.
```
CurrentTime()
```

CurrentTimeMS

Returns the time, including microseconds, at which the job runs.

Input: Not applicable
Output: time (time)
Examples. Use this function to add a new column that contains the time to the output data by the Transformer stage. You must set the Extended field in the column metadata to Microseconds to contain the full time.
```
CurrentTimeMS()
```

CurrentTimestamp

Returns a time stamp that gives the date and time that the job runs.

Input: Not applicable
Output: time stamp (timestamp)
Examples. Use this function to add a new column that contains the time stamp to the output data by the Transformer stage.
```
CurrentTimestamp()
```

CurrentTimestampMS

Returns a time stamp, including microseconds, that gives the date and time that the job runs.

Input: Not applicable
Output: time stamp (timestamp)
Examples. Use this function to add a new column that contains the time stamp to the data output by the Transformer stage. You must set the Extended field in the column metadata to Microseconds to contain the full time stamp.
```
CurrentTimestampMS()
```

ConvertTimeZone

Converts a timestamp with time zone to a different time zone.

Input: year of time zone to convert to (int), month of time zone to convert to (int), day of time zone to convert to (int), hour of time zone to convert to (int), minutes of time zone to convert to (int), seconds of time zone to convert to (int), three-letter time zone acronym to convert from (string), three-letter time zone acronym to convert to (string)cable
Output: new time zone (string)
Examples. If mylink.currentYear contains the integer 2021, then the following function returns a timestamp string that uses the new time zone, "2021-09-30 04:02:03".
```
ConvertTimeZone(mylink.currentYear, 9, 30, 1, 2, 3, "PST", "EST")
```

DateFromDaysSince

Returns a date string by adding an integer to a baseline date. The integer can be negative to return a date that is earlier than the baseline date. This function is deprecated in IBM® InfoSphere® Information Server, Version 11.3. Use the DateFromDaysSince2 function.

Input: number (int32), [baseline_date_string (string)]
Output: date (date)
Examples. If mylink.myintcol contains the integer 18250, and mylink.mydatecol contains the date 1958–08–18, then the three following functions are equivalent, and return the date 2008–08–05:
```
DateFromDaysSince(18250,"1958-08-18")
DateFromDaysSince(mylink.myintcol,"1958-08-18")
DateFromDaysSince(mylink.myintcol,mylink.mydatecol)
```
If mylink.mynegintcol contains the integer -1, and mylink.mydatecol contains the date 1958–08–18, then the following three functions are equivalent, and return the date 1958–08–17:
```
DateFromDaysSince(-1,"1958-08-18")
DateFromDaysSince(mylink.mynegintcol,"1958-08-18")
DateFromDaysSince(mylink.mynegintcol,mylink.mydatecol)
```

DateFromDaysSince2

Returns a date object by adding an integer to a baseline date. The integer can be negative to return a date that is earlier than the baseline date.

Input: number (int32), [baseline_date_object (date)]
Output: date (date)
Examples. If mylink.myintcol contains the integer 18250, and mylink.mydatecol contains the date 1958–08–18, then the three following functions are equivalent, and return the date 2008–08–05:
```
DateFromDaysSince2(18250,"1958-08-18")
DateFromDaysSince2(mylink.myintcol,"1958-08-18")
DateFromDaysSince2(mylink.myintcol,mylink.mydatecol)
```
If mylink.mynegintcol contains the integer -1, and mylink.mydatecol contains the date 1958–08–18, then the following three functions are equivalent, and return the date 1958–08–17:
```
DateFromDaysSince2(-1,"1958-08-18")
DateFromDaysSince2(mylink.mynegintcol,"1958-08-18")
DateFromDaysSince2(mylink.mynegintcol,mylink.mydatecol)
```

DateFromComponents

Returns a date from the given years, months, and day of month that is given as three separate values.

Input: years (int32), months (int32), dayofmonth (int32)
Output: date (date)
Examples. If mylink.yearcol contains the value 2010, mylink.monthcol contains the value 12, and mylink.dayofmonthcol contains the value 2, then the two following functions are equivalent, and return the date 2010–12–02.
```
DateFromComponents(2010, 12, 2)
DateFromComponents(mylink.yearcol, mylink.monthcol, mylink.dayofmonthcol)
```

DateFromJulianDay

Returns a date from the given Julian day number.

Input: julianday (uint32)
Output: date (date)
Examples. If mylink.myjulcol contains the value 2454614, then the two following functions are equivalent, and return the date 2008–05–27.
```
DateFromJulianDay(2454614)
DateFromJulianDay(mylink.myjulcol)
```

DateOffsetByComponents

Returns the given date, with offsets applied from the given year offset, month offset, and day of month offset, given as three separate values. The offset values can each be positive, zero, or negative.

Input: basedate (date), year_offset (int32), month_offset (int32), dayofmonth_offset (int32)
Output: date (date)
Examples. If mylink.basedate contains 2011-08-18 and mylink.yearos contains the value 2, mylink.monthos contains the value 0, and mylink.dayofmonthosol contains the value 0, then the two following functions are equivalent, and return the date 2013–08–18.
```
DateOffsetByComponents("2011-08-18", 2, 0, 0)
DateOffsetByComponents(mylink.basedate, mylink.yearos, mylink.monthos, 
mylink.dayofmonthos)
```
If mylink.basedate contains 2011-08-18 and mylink.yearos contains the value -2, mylink.monthos contains the value 0, and mylink.dayofmonthosol contains the value 0, then the two following functions are equivalent, and return the date 2009–08–18.
```
DateOffsetByComponents("2011-08-18", -2, 0, 0)
DateOffsetByComponents(mylink.basedate, mylink.yearos, mylink.monthos, 
mylink.dayofmonthos)
```

DaysSinceFromDate

Returns the number of days from the source date to the given date. This function is deprecated in IBM InfoSphere Information Server, Version 11.3. Use the DaysSinceFromDate2 function.

Input: given_date (date), source_date_string (string)
Output: days_since (int32)
Examples. If mylink.mysourcedate contains the date 1958–08–18 and mylink.mygivendate contains the date 2008–08–18, then the two following functions are equivalent, and return the integer value 18263.
```
DaysSinceFromDate(mylink.mygivendate, mylink.mysourcedate)
DaysSinceFromDate("2008-08-18","1958-08-18")
```

DaysSinceFromDate2

Returns the number of days from the source date to the given date.

Input: given_date (date), source_date_object (date)
Output: days_since (int32)
Examples. If mylink.mysourcedate contains the date 1958–08–18 and mylink.mygivendate contains the date 2008–08–18, then the two following functions are equivalent, and return the integer value 18263.
```
DaysSinceFromDate2(mylink.mygivendate, mylink.mysourcedate)
DaysSinceFromDate2("2008-08-18","1958-08-18")
```

DaysInMonth

Returns the number of days in the month in the given base date.

Input: basedate (date)
Output: daysinmonth (int32)
Examples. If mylink.mysourcedate contains the date 1958–08–18, then the two following functions are equivalent, and return the integer value 31.
```
DaysInMonth(mylink.mysourcedate)
DaysInMonth("1958-08-18")
```

DaysInYear

Returns the number of days in the year in the given base date.

Input: basedate (date)
Output: daysinyearh (int32)
Examples. If mylink.mysourcedate contains the date 2012–08–18, then the two following functions are equivalent, and return the integer value 366.
```
DaysInYear(mylink.mysourcedate)
DaysInYear("2012-08-18")
```
If mylink.mysourcedate contains the date 2011–08–18, then the two following functions are equivalent, and return the integer value 365.
```
DaysInYear(mylink.mysourcedate)
DaysInYear("2011-08-18")
```

DateOffsetByDays

Returns the given date offset by the given number of days. The offset value can be positive, zero, or negative.

Input: basedate (date), dayoffset (int32)
Output: date (date)
Examples. If mylink.basedate contains 2011-08-18 and mylink.dayoffset contains the value 2, then the two following functions are equivalent, and return the date 2011–08–20.
```
DateOffsetByDays("2011-08-18", 2)
DateOffsetByDays(mylink.basedate, mylink.dayoffset)
```
If mylink.basedate contains 2011-08-18 and mylink.dayoffset contains the value -31, then the two following functions are equivalent, and return the date 2011–07–18.
```
DateOffsetByDays("2011-08-18", -31)
DateOffsetByDays(mylink.basedate, mylink.dayoffset)
```

HoursFromTime

Returns the hours portion of a time.

Input: time (time)
Output: hours (int8)
Examples. If mylink.mytime contains the time 22:30:00, then the following two functions are equivalent, and return the integer value 22.
```
HoursFromTime(mylink.mytime)
HoursFromTime("22:30:00")
```

JulianDayFromDate

Returns a Julian day number from the given date.

Input: date (date)
Output: julianday (int32)
Examples. If mylink.mydate contains the date 2008–05–27, then the two following functions are equivalent, and return the value 2454614.
```
JulianDayFromDate("2008–05–27")
JulianDayFromDate(mylink.mydate)
```

MicroSecondsFromTime

Returns the microsecond portion of a time.

Input: time (time)
Output: microseconds (int32)
Examples. If mylink.mytime contains the time 22:30:00.32, then the following function returns the value 320000:
```
MicroSecondsFromTime(mylink.mytime)
```

MidnightSecondsFromTime

Returns the number of seconds from midnight to the given time.

Input: time (time)
Output: seconds (int8)
Examples. If mylink.mytime contains the time 00:30:52, then the two following functions are equivalent, and return the value 1852:
```
MidnightSecondsFromTime("00:30:52")
MidnightSecondsFromTime(mylink.mytime)
```

MinutesFromTime

Returns the minutes portion of a time.

Input: time (time)
Output: minutes (int8)
Examples. If mylink.mytime contains the time 22:30:52, then the two following functions are equivalent, and return the value 30:
```
MinutesFromTime("22:30:52")
MinutesFromTime(mylink.mytime)
```

MonthDayFromDate

Returns the day of the month from the given date.

Input: date (date)
Output: day (int8)
Examples. If mylink.mydate contains the date 2008-08-18, then the two following functions are equivalent, and return the value 18:
```
MonthDayFromDate("2008-08-18")
MonthDayFromDate(mylink.mydate)
```

MonthFromDate

Returns the month number from the given date.

Input: date (date)
Output: month_number (int8)
Examples. If mylink.mydate contains the date 2008-08-18, then the two following functions are equivalent, and return the value 8:
```
MonthFromDate("2008-08-18")
MonthDayDate(mylink.mydate)
```

NextWeekdayFromDate

Returns the date of the specified day of the week soonest after the source date. The day of the week is specified as the full name, for example, thursday, or a three-letter abbreviation, for example, thu.

Input: sourcedate (date), day_of_week (string)
Output: date (date)
Examples. If mylink.mysourcedate contains the date 2008-08-18 and the day of the week that is specified is Thursday, then the two following functions are equivalent, and return the value 2008–08–21:
```
NextWeekdayFromDate("2008-08-18", "thursday")
NextWeekdayFromDate(mylink.mysourcedate, "thu")
```

NthWeekdayFromDate

Returns the date of the specified day of the week offset by the specified number of weeks from the source date. The day of the week is specified as the full name, for example, thursday, or a three-letter abbreviation, for example, thu. The offset values can be positive, negative, or zero.

Input: basedate (date), day_of_week (string), week_offset (int32)
Output: date (date)
Examples. If mylink.mydate contains the date 2009-08-18 and Thursday is specified with an offset of 1, then the two following functions are equivalent, and return the value 2009–08–20:
```
NthWeekdayFromDate("2009-08-18", "thursday", 1)
NthWeekdayFromDate(mylink.mydate, "thu", 1)
```
The first occurrence of Thursday is returned. In the proceeding example, the Thursday occurs in the same week as the date 2009-08-18. The date 2009-08-18 is a Tuesday.
If mylink.mydate contains the date 2009-08-18 and Thursday is specified with an offset of -2, then the two following functions are equivalent, and return the value 2009–08–06:
```
NthWeekdayFromDate("2009-08-18", "thursday", -2)
NthWeekdayFromDate(mylink.mydate, "thu", -2)
```
The occurrence of Thursday that is two Thursdays past is returned.

PreviousWeekdayFromDate

Returns the date of the specified day of the week that is the most recent day before the source date. The day of the week is specified as the full name, for example, thursday, or a three-letter abbreviation, for example, thu.

Input: sourcedate (date), day_of_week (string)
Output: date (date)
Examples. If mylink.mysourcedate contains the date 2008-08-18 and Thursday is specified, then the two following functions are equivalent, and return the value 2008–08–14:
```
PreviousWeekdayFromDate("2008-08-18", "thursday")
PreviousWeekdayFromDate(mylink.mysourcedate, "thu")
```

SecondsFromTime

Returns the seconds portion of a time.

Input: time (time)
Output: seconds (dfloat)
Examples. If mylink.mytime contains the time 22:30:52, then the two following functions are equivalent, and return the value 52:
```
SecondsFromTime("22:30:52")
SecondsFromTime(mylink.mytime)
```

SecondsSinceFromTimestamp

Returns the number of seconds between two time stamp strings. This function is deprecated in IBM InfoSphere Information Server, Version 11.3. Use the SecondsSinceFromTimestamp2 function.

Input: time stamp (timestamp), timestamp_base_string (string)
Output: seconds (dfloat)
Examples. If mylink.mytimestamp contains the time stamp 2008–08–18 22:30:52, and mylink.mytimestamp_base contains the time stamp 2008–08–19 22:30:52, then the two following functions are equivalent, and return the value -86400:
```
SecondsSinceFromTimestamp("2008–08–18 22:30:52","2008–08–19 22:30:52")
SecondsSinceFromTimestamp(mylink.mytimestamp, mylink.mytimestamp_base)
```

SecondsSinceFromTimestamp2

Returns the number of seconds between two time stamp objects.

Input: time stamp (timestamp), timestamp_base_object (timestamp)
Output: seconds (dfloat)
Examples. If mylink.mytimestamp contains the time stamp 2008–08–18 22:30:52, and mylink.mytimestamp_base contains the time stamp 2008–08–19 22:30:52, then the two following functions are equivalent, and return the value -86400:
```
SecondsSinceFromTimestamp2("2008–08–18 22:30:52","2008–08–19 22:30:52")
SecondsSinceFromTimestamp2(mylink.mytimestamp, mylink.mytimestamp_base)
```

TimeDate

Returns the system time and date as a formatted string.

Input: Not applicable
Output: system time and date (string)
Examples. If the job was run at 4.21 pm on June 20th 2008, then the following function returns the string 16:21:48 20 Jun 2008.
```
TimeDate()
```

TimeFromComponents

Returns a time from the given hours, minutes, seconds, and microseconds, given as four separate values.

Input: hours (int32), minutes (int32), seconds (int32), microseconds (int32)
Output: time (date)
Examples. If mylink.hourcol contains the value 10, mylink.mincol contains the value 12, mylink.seccol contains the value 2, and mylink.mseccol contains 0, then the two following functions are equivalent, and return the time 10:12:02.0:
```
TimeFromComponents(10, 12, 2, 0)
TimeFromComponents(mylink.hourcol, mylink.mincol, mylink.seccol, mylink.mseccol)
```

TimeFromMidnightSeconds

Returns the time given the number of seconds since midnight.

Input: seconds (dfloat)
Output: time (time)
Examples. If mylink.mymidnightseconds contains the value 240, then the two following functions are equivalent, and return the value 00:04:00:
```
TimeFromMidnightSeconds("240")
TimeFromMidnightSeconds(mylink.mymidnightseconds)
```

TimeOffsetByComponents

Returns the time, with offsets applied from the base time with hour offset, minute offset, and second offset, each given as separate values. The seconds offset can include partial seconds.

Input: basetime (time), hour_offset (int32), minute_offset (int32), second_offset (dfloat)
Output: time (time)
Examples. If mylink.basetime contains 14:05:29 and mylink.houros contains the value 2, mylink.minos contains the value 0, mylink.secos contains the value 20, then the two following functions are equivalent, and return the time 16:05:49.
```
TimeOffsetByComponents("14:05:29", 2, 0, 20)
TimeOffsetByComponents(mylink.basetime, mylink.houros, mylink.minos, mylink.secos)
```

TimeOffsetBySeconds

Returns the given time, with offsets applied from the base time with seconds offset. The seconds offset can include partial seconds.

Input: basetime (time), second_offset (dfloat)
Output: time (time)
Examples. If mylink.basetime contains 14:05:29.30 and mylink.secos contains the value 2.5, then the two following functions are equivalent, and return the time 14:05:31.80.
```
TimeOffsetByComponents("14:05:29.30", 2.5)
TimeOffsetByComponents(mylink.basetime, mylink.secos)
```

TimestampFromDateTime

Returns a time stamp from the given date and time.

Input: date (date), time (time)
Output: time stamp (timestamp)
Examples. If mylink.mydate contains the date 2008–08–18 and mylink.mytime contains the time 22:30:52, then the two following functions are equivalent, and return the time stamp 2008–08–18 22:30:52:
```
TimestampFromDateTime("2008–08–18","22:30:52")
TimestampFromDateTime(mylink.mydate,mylink.mytime)
```

TimestampFromSecondsSince

Returns a time stamp that is derived from the number of seconds from the base time stamp string. This function is deprecated in IBM InfoSphere Information Server, Version 11.3. Use the TimestampFromSecondsSince2 function.

Input: seconds (dfloat), [base_timestamp_string (string)]
Output: time stamp (timestamp)
Examples. If mylink.myseconds contains the value 2563 and mylink.timestamp_base contains the time stamp 2008–08–18 22:30:52, then the two following functions are equivalent, and return the time stamp 2008–08–18 23:13:35:
```
TimestampFromSecondsSince("2563","2008–08–18 22:30:52")
TimestampFromSecondsSince(mylink.myseconds,mylink.timestamp_base)
```

TimestampFromSecondsSince2

Returns a time stamp that is derived from the number of seconds from the base time stamp object.

Input: seconds (dfloat), [base_timestamp_object (timestamp)]
Output: time stamp (timestamp)
Examples. If mylink.myseconds contains the value 2563 and mylink.timestamp_base contains the time stamp 2008–08–18 22:30:52, then the two following functions are equivalent, and return the time stamp 2008–08–18 23:13:35:
```
TimestampFromSecondsSince2("2563","2008–08–18 22:30:52")
TimestampFromSecondsSince2(mylink.myseconds,mylink.timestamp_base)
```

TimestampFromTimet

Returns a time stamp from the given UNIX time_t string value.

Input: timet_string (int32)
Output: timestamp
Examples. If mylink.mytimet contains the value 1234567890, then the two following functions are equivalent, and return the time stamp 2009–02–13 23:31:30:
```
TimestampFromTimet("1234567890")
TimestampFromTimet(mylink.mytimet)
```

TimestampFromTime2

Returns a time stamp from the given time and timestamp objects. The value in the time object overwrites the time value in the time stamp object so that only the date part is used from the time stamp.

Input: time (time), time stamp (timestamp)
Output: time stamp (timestamp)
Examples. If mylink.mytime contains the time 12:03:22 and mylink.mytimestamp contains the time stamp 2008–08–18 22:30:52, then the two following functions are equivalent, and return the time stamp 2008–08–18 12:03:22:
```
TimestampFromTime2("12:03:22", "2008–08–18 22:30:52")
TimestampFromTime2(mylink.mytime,mylink.mytimestamp)
```

TimestampOffsetByComponents

Returns the time stamp, with offsets applied from the base time stamp with year offset, month offset, day offset, hour offset, minute offset, and second offset, each given as separate values. The seconds offset can include microseconds.

Input: basetimestamp (timestamp), year_offset (int32), month_offset (int32), dayofmonth_offset (int32), hour_offset (int32), minute_offset (int32), second_offset (dfloat)
Output: time stamp (timestamp)
Examples. If mylink.basetimestamp contains 2009-08-18 14:05:29 and mylink.yearos contains 0, mylink.monthos contains the value 2, mylink.dayos contains the value -4, mylink.houros contains the value 2, mylink.minos contains the value 0, mylink.secos contains the value 20, then the two following functions are equivalent, and return the time stamp 2009-10-14 16:05:49.
```
TimestampOffsetByComponents("2009-08-18 14:05:29", 0, 2, -4, 2, 0, 20)
TimestampOffsetByComponents(mylink.basetimestamp, mylink.houros, 
mylink.minos, mylink.secos)
```

TimestampOffsetBySeconds

Returns the time stamp, with offsets applied from the base time stamp with seconds offset. The seconds offset can include microseconds.

Input: basetimestamp (timestamp), second_offset (dfloat)
Output: time stamp (timestamp)
Examples. If mylink.basetimestamp contains 2009-08-18 14:05:29 and mylink.secos contains the value 32760, then the two following functions are equivalent, and return the time stamp 2009-08-18 23:11:29:
```
TimeOffsetBySeconds("2009-08-18 14:05:29", 32760)
TimeOffsetBySeconds
(mylink.basetimestamp, mylink.secos)
```

TimetFromTimestamp

Returns a UNIX time_t value from the given time stamp.

Input: time stamp (timestamp)
Output: timet (int32)
Examples. If mylink.mytimestamp contains the value 2009–02–13 23:31:30, then the two following functions are equivalent, and return the value 1234567890:
```
TimestampFromTimet("2009–02–13 23:31:30")
TimestampFromTimet(mylink.mytimestamp)
```

WeekdayFromDate

Returns the day number of the week from the given date. Origin_day optionally specifies the day that is regarded as the first in the week and is Sunday by default.

Input: date (date), [origin_day (int8)]
Output: day (int8)
Examples. If mylink.mydate contains the date 2008-08-18, then the two following functions are equivalent, and return the value 1:
```
WeekdayFromDate("2008-08-18")
WeekdayFromDate(mylink.mydate)
```
If mylink.mydate contains the date 2008-08-18, and mylink.origin_day contains saturday, then the two following functions are equivalent, and return the value 2:
```
WeekdayFromDate("2008-08-18","saturday")
WeekdayFromDate(mylink.mydate,mylink.origin_day)
```

YeardayFromDate

Returns the day number in the year from the given date.

Input: date (date)
Output: day (int16)
Examples. If mylink.mydate contains the date 2008-08-18, then the two following functions are equivalent, and return the value 231:
```
YeardayFromDate("2008-08-18")
YeardayFromDate(mylink.mydate)
```

YearFromDate

Returns the year from the given date.

Input: date (date)
Output: year (int16)
Examples. If mylink.mydate contains the date 2008-08-18, then the two following functions are equivalent, and return the value 2008:
```
YearFromDate("2008-08-18")
YearFromDate(mylink.mydate)
```

YearweekFromDate

Returns the week number in the year from the given date.

Input: date (date)
Output: week (int16)
Examples. If mylink.mydate contains the date 2008-08-18, then the two following functions are equivalent, and return the value 33:
```
YearweekFromDate("2008-08-18")
YearweekFromDate(mylink.mydate)
```