Working with Dates and Times

Working with dates and times is a notoriously difficult problem, the most famous incantation being Y2K. Airkit provides a comprehensive system to work with dates and times for this precise reason. There are three data types to familiarize yourself with, the Datetime, the Date, and the Time types. The best way to think of these is by analogy. A Datetime is a specific time marked on your calendar. A Date is a particular day's square on your calendar. A Time is a particular configuration of your clock.

If you would like to find the current Datetime you can use the NOW function.


Other ways to create a date/time are to use the functions DATETIME, DATE, and TIME. Using January 9th, 2007 9:41 AM as an example Datetime:

DATETIME(DATE(2007, 1, 9), TIME(9, 41, 0), "America/Los_Angeles")  
DATE(2007, 1, 9)  
TIME(9, 41, 0)

To retrieve the year of our example Date use the YEAR function:


The YEAR function can also be used to retrieve the year from our example Datetime:


Use the DATE_FROM_DATETIME and the TIME_FROM_DATETIME functions to convert a Datetime to a Date or Time respectively:


The following functions can be used to retrieve different parts of Datetimes, Dates, and Times

Function NameDescription
DATE_FROM_DATETIMEReturns the date portion of a Datetime.
TIME_FROM_DATETIMEReturns the time portion of a Datetime.
YEARReturns the year of a Datetime or Date
MONTHReturns the month of a Datetime or Date
DAYReturns the day of a Datetime or Date
HOURReturns the hour of a Datetime or Time
MINUTEReturns the minute of a Datetime or Time
SECONDReturns the second of a Datetime or Time


The FORMAT_DATETIME, FORMAT_DATE, and FORMAT_TIME functions allow you to create a String from a Datetime, Date, or Time. Use these functions when you would like to display a Datetime, Date, or Time to a user so that Airkit can format it correctly according to the user's locale and timezone. For example, to display our example Datetime as January 9, 2007 9:41 AM PST:

FORMAT_DATETIME(example_datetime, "MMMM do, YYYY h:mm A")

or just the time as 9:41 AM:

FORMAT_DATETIME(example_datetime, "h:mm A")


Modifying with a Specific Value

If you want to change a particular portion of a Datetime, there are several functions that will allow you to do so. For example, if you wanted to change the year of our example Datetime from 2007 to 2010, you would use the UPDATE_YEAR function to create a new Datetime by changing only the year:

UPDATE_YEAR(example_datetime, 2010)

This function works equally well on Dates:

UPDATE_YEAR(example_datetime, 2010)

If you wanted to update the calendar date of the Datetime, you can use the UPDATE_DATE function to create a new Datetime, with a new calendar date, but the same time:

UPDATE_DATE(example_datetime, DATE(2010, 4, 3))

DatetimesDates, and Times can be modified using the following functions

Function NameDescription
UPDATE_DATEChanges the date portion of a Datetime.
UPDATE_TIMEChanges the time portion of a Datetime.
UPDATE_TIMEZONEChanges the timezone of a Datetime.
UPDATE_YEARChanges the year of a Datetime or Date
UPDATE_MONTHChanges the month of a Datetime or Date
UPDATE_DAYChanges the day of a Datetime or Date
UPDATE_HOURChanges the hour of a Datetime or Time
UPDATE_MINUTEChanges the minute of a Datetime or Time
UPDATE_SECONDChanges the second of a Datetime or Time
UPDATE_MILLISECONDChanges the millisecond of a Datetime or Time

Modifying with a Relative Value

The ADD_TO_DATETIME and SUBTRACT_FROM_DATETIME functions allow you to create a new date by adding or removing time units. You could add six months, or 169 days to our example date:

ADD_TO_DATETIME(example_datetime, 6, "M")  
ADD_TO_DATETIME(example_datetime, 169, "d")


When interfacing with external systems, you'll often encounter Datetimes that are represented either as a Number or a String.

Datetime as a Number

An Epoch timestamp is a Number representing the number of seconds (or milliseconds) that have elapsed since January 1st, 1970. For example the timestamp in milliseconds for January 9ith, 2007 is 1168364460000. When you encounter a Datetime in this format look to the DATETIME_FROM_TIMESTAMP function which turns an Epoch timestamp measured in milliseconds into a proper Datetime.


To go in the other direction look no further than the TIMESTAMP_FROM_DATETIME function.


If your particular timestamp looks like it is missing three zeros than the previous example, you are likely working with timestamps in seconds, provide second parameter "s"

DATETIME_FROM_TIMESTAMP(1168364460, "s")  
TIMESTAMP_FROM_DATETIME(example_datetime, "s")

Datetime as a String

The other commonly used timestamp type is a string with the format "YYYY-MM-DDTHH:mm:ssZ". To convert between a timestamp of this format and a Datetime use the DATETIME_FROM_FORMAT and FORMAT_DATETIME functions.



Note that one of our previous examples reproduced below included a timezone:

DATETIME(DATE(2007, 1, 9), TIME(9, 41, 0), "America/Los_Angeles")

This is important because, in addition to a Date and a Time, Datetimes also have an associated timezone.

Relevant Functions

Did this page help you?