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)