Using Date/Time Data in Tables

User Guide > Creating and Querying Tables > Using Date/Time Data in Tables

In the previous examples, the DATE column contains long integer values that represent the dates of calls. Instead of using long integers to represent the dates, you may be able to read the date data into a date/time variable. Once in the Date/Time format, the dates can be converted to strings, placed in a table, and manipulated with QUERY_TABLE. In addition, query results can be converted back into Date/Time form and plotted on with a Date/Time axis.

Read the Date Data into a Date/Time Variable

Instead of reading the date data (901002, 901003, etc.) into a long integer, read it into an array of Date/Time variables. For detailed information on reading date data, see "Reading in Your Date/Time Data". This section contrasts the various alternatives you have available for reading date/time data.

Two Methods of Handling Date/Time Data in Tables

This section discusses two ways to handle Date/Time data in a table. It assumes that data has been read into a Date/Time variable. The first method discussed involves converting the Date/Time variable to a string variable, which you can use to build and subset a table. The second method involves manipulating the Date/Time data directly as Julian day values.

Method 1: Convert the Date/Time Data to Strings

Convert the Date/Time variable to a String using the DT_TO_STR procedure. For example:

DT_TO_STR, dtdata, dates, Date_Fmt=5

This converts the Date/Time values into strings of the format [YY]YY*MM*DD. The advantage of this format is that it allows dates to be compared directly as strings. For example:

"1992-02-01"

precedes:

"1993-03-02"

Subsetting the Table

Once you have created string variables from the original Date/Time data, you can build a table using these string variables, and use the strings in query commands:

this_date=QUERY_TABLE(phone_data, '* Where DATE = "1990-10-03"')

Plotting the Table with a Date/Time Axis

To plot the table with a Date/Time axis, you have to first convert the dates back into Date/Time data. To do this, use the STR_TO_DT function. For example:

PLOT, STR_TO_DT(phone_data.DATES), phone_data.COST

Method 2: Create a Table that Includes the Date/Time Variable

This method deals directly with the Julian day part of the Date/Time structure. Assuming that the Date/Time variable is called DATE, the following commands create a new table containing three columns:

; Create a new variable JDATE that contains the Julian date

; equivalents for each date. This is necessary because you cannot

; place a Date/Time structure directly in a table; tables must

; consist of vector (one-dimensional array) variables only.

JDATE=DATE.Julian

; Create the table.

new_ph_tbl = BUILD_TABLE("EXT, COST, JDATE")

Subsetting the Table

The following query picks out all rows where DATE is less than or equal to October 3, 1990:

; Create a Date/Time variable called END_DATE, and set the

; variable equal to the Julian equivalent of October 3, 1990.

TDate = VAR_TO_DT(90,10,03)

END_DATE = TDate.Julian

; Produce a subset of the table.

New_Table = QUERY_TABLE(new_ph_tbl, '* where JDATE <= END_DATE')

Plotting the Table with a Date/Time Axis

To plot the resulting table data with a Date/Time axis, the date data must be converted back to a Date/Time variable. The following command performs the conversion:

New_Dates = JUL_TO_DT(new_ph_tbl.JDATE)

When the data is plotted, PV‑WAVE determines that New_Dates is a Date/Time variable, and plots a Date/Time axis automatically. For example:

; Plot the dates on the x-axis and the cost on the y-axis.

PLOT, New_Dates, new_ph_tbl.COST

For more information on plotting table data, see "Plotting Table Data".

Version 2017.0