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.
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:
The following functions can be used to retrieve different parts of Datetimes, Dates, and Times
|DATE_FROM_DATETIME||Returns the date portion of a Datetime.|
|TIME_FROM_DATETIME||Returns the time portion of a Datetime.|
|YEAR||Returns the year of a Datetime or Date|
|MONTH||Returns the month of a Datetime or Date|
|DAY||Returns the day of a Datetime or Date|
|HOUR||Returns the hour of a Datetime or Time|
|MINUTE||Returns the minute of a Datetime or Time|
|SECOND||Returns 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")
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:
This function works equally well on Dates:
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))
Datetimes, Dates, and Times can be modified using the following functions
|UPDATE_DATE||Changes the date portion of a Datetime.|
|UPDATE_TIME||Changes the time portion of a Datetime.|
|UPDATE_TIMEZONE||Changes the timezone of a Datetime.|
|UPDATE_YEAR||Changes the year of a Datetime or Date|
|UPDATE_MONTH||Changes the month of a Datetime or Date|
|UPDATE_DAY||Changes the day of a Datetime or Date|
|UPDATE_HOUR||Changes the hour of a Datetime or Time|
|UPDATE_MINUTE||Changes the minute of a Datetime or Time|
|UPDATE_SECOND||Changes the second of a Datetime or Time|
|UPDATE_MILLISECOND||Changes the millisecond of a Datetime or Time|
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.
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")
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.
Updated 3 months ago