Using Date- and Time-Based Data in Maps and Queries - MapInfo_Pro - 2023

If you maintain time- or date/time-specific data, you may be interested in querying that information and displaying it visually in your maps. Time and DateTime data types let you display that data thematically and as part of a query analysis. You could use this data type for many types of projects, such as to display crime information based on Date and Time or to post schedules based on resource availability on a particular date. Also, you can use the date and time data from Access, Excel, dBase, and remote databases.

Time and DateTime data types options are made available in the lists of your Query dialog boxes.

Using Time and Time/Date Data Types

You can convert existing fields with time or time/date information into Time and Time/Date formats, so you can use them for querying and thematic maps.

To convert your data:

1. Open the data you want to convert in MapInfo Pro.
2. On the TABLE tab, in the Maintenance group, click Table, and Modify Structure, and select the table for which you want to change the data type. The Modify Table Structure dialog box displays.
   
   

   

   
   
3. To change the ArrivalTime field from a character field to a Time field, click the Type drop-down arrow and select the Time type.
4. Click OK to save your change.

Adding Time or DateTime Data Types to Existing Data

If you have existing data and want to add Time or DateTime content to it, check the following table to ensure that the data is in a format that MapInfo Pro can recognize as Time or DateTime.

where:

HH refers to hours, mm refers to minutes, ss refers to seconds, ff refers to fractions of seconds, yyyy refers to years, MM refers to month, dd refers to date

Converting Existing Data to Time or DateTime Data Types

If you have Time or DateTime data in your tables already, check to see if it is in the format you want by checking the Time and DateTime Data Formats. If you have a lot of data and it is not in the format you require for your tasks, you can use one of the MapBasic conversion functions to assist you with this process.

Additional Notes for Converting a String

* to a Date

If the year is last in the current Date format, you can omit it and MapInfo Pro will assume the current year. If the data specifies a two-digit year, MapInfo Pro assigns the century based on the current Date Window. If the Date is invalid the value is set to null and MapInfo Pro may display the error, "Could not convert data."

** to a Time

MapInfo Pro accepts both military and AM/PM forms. The data can specify the entire locale in AM or PM strings or use just the first character of each. The data may omit the milliseconds, seconds, and minutes if the lower-order portions are also omitted. If the Time is invalid the value is set to null and MapInfo Pro may display the error, "Could not convert data."

*** to a DateTime

The conversion rules for Date and Time apply for the respective portions of the string. If the data omits the Time portion, MapInfo Pro assumes midnight. If the DateTime is invalid the value is set to null and MapInfo Pro may display the error, "Could not convert data."

Additional Notes for Converting a Number to a String

# The conversion rules for Date and Time apply for the respective portions of the string. If the data omits the Time portion MapInfo Pro assumes the Time is midnight. If the DateTime is invalid the value is set to null and MapInfo Pro may display the error, "Could not convert data.".

If you have a lot of data and it is not in the format you require for your tasks, you can use one of the MapBasic conversion functions to assist you with this process.

Creating a DateTime Column from Two Separate Columns

To create a DateTime column from a Date column and a Time column:

1. Open your data in MapInfo Pro Browser window. We use CrimeActivity.TAB from the Introductory Data on your DVD.
2. On the TABLE tab, in the Maintenance group, click Table, and Modify Structure to display the Modify Table Structure dialog box.
3. Click Add Field and type a Date_Time label in the Name field.
4. Select Date/Time in the Type drop-down list and click OK.
5. On the TABLE tab, in the Edit group, click Update Column to display the Update Column dialog box.
6. Select the table name in the Table to Update drop-down list.
7. Select the name of the new column you just created in the Column to Update drop-down list.
8. Select the table name again in the Get Value from Table drop-down list.
9. Click the Assist button to display the Expressions dialog box.
10. Use the Column drop-down list to create the following expression and click OK:

	DateColumnName + TimeColumnName

For more about using math expressions with Time and Date information, see Using Arithmetic Operators with Time and Time/Date Data Types.

1. Click OK to update the new column with the Date and Time information.

Creating Thematic Maps using Time and Time/Date Data Types

For ranged thematic maps, Time values will be able to be rounded by seconds, minutes, and hours. The default is seconds. When you select None, MapInfo Pro does not round the electing the seconds and displays a granularity of milliseconds.

To create a thematic map using DateTime information:

1. Open your data in MapInfo Pro Browser window. Use the appropriate background map to give your data perspective. Here we use the Great Britain data in the Introductory Data on the MapInfo Pro DVD.
2. On the MAP tab, in the Selection group, click SQL Select to display the SQL Select dialog box.
3. Place your cursor in the from Tables field and select the table name that contains the DateTime data in the from Tables drop-down list. For our example we used the CrimeActivity table.
4. To find out the location of crimes between April 10, 2003 and April 10, 2004 and between the hours of 12 and 8 p.m., we entered this text in the where Condition box:

   	Crime_Date between "04/10/2003" and "04/10/2004" And Crime_Time between
   				"12:00:00 PM" and "08:00:00 PM"

1. Select the Browse Results and Find Results in Current Map Window check boxes to display the results.
2. Click OK to create the query and display the results.
   
   

   

   
   

Using Arithmetic Operators with Time and Time/Date Data Types

You can use the addition and subtraction operators with the Time and Time/Date data types as follows:

Using Comparison Operators with Time and Time/Date Data Types

The comparison operators are =, <>, <, >, <=, >= and should all work as expected for the Time and DateTime values. For Time values, the smallest Time is "12:00:00.000 AM" while the largest Time is "11:59:59.999 PM".

The "Between" operator works as expected for DateTime values. For Time values, we support wrap-around comparisons. For example:

TimeValue Between "2:00 AM" And "10:00 PM" is true if the TimeValue is greater than or equal to "2:00 AM" and less than or equal to "10:00 PM"

TimeValue Between "10:00 PM" And "2:00 AM" is true if the TimeValue is greater than or equal to "10:00 PM" and less than or equal to "11:59:59.999" or greater than or equal to "12:00 AM" and less than or equal to "2:00 AM"

Using Logical Operators with Time and Time/Date Data Types

A Time or DateTime field with a value is true, while the same field with a null value indicates false. The behavior of the logical operators And, Or, and Not should follow logically from this.

Interpreting Excel Data

Using Excel you can format numeric cells as Dates and/or Times. Excel determines the meaning of the 'm' format code as either 'month' or 'minute' based on the rest of the format code.

See Also:

Ensuring that Dates Display Correctly for Excel for Macintosh Tables

Controlling how MapInfo Pro Handles Mixed Data Type Columns

Interpreting Microsoft Access Data

Access data uses a DateTime type, but not separate Date or Time types. MapInfo Pro registers the Access DateTime type fields as DateTime fields.

When you save a MapInfo table to Access format, MapInfo Pro writes Date, Time, and DateTime fields as Access DateTime fields. The .TAB file itself maintains the Date, Time, and DateTime types so when you reopen the file, the Date, Time, or DateTime data displays. If you attempt to open these .TAB files using a non-MapInfo application, these fields display as DateTime.

Interpreting dBase Data

The dBase format supports Date fields but does not explicitly support Time or DateTime fields. Therefore, when you register an existing dBase file, there will be no Time or DateTime fields. When you save or export a MapInfo table with Time or DateTime fields to a dBase file, Time and DateTime fields are written out as character fields of length 9 and 17, respectively so that the data is written out in numeric formats.

When you save a MapInfo table to dBase format, the .TAB file maintains the Time and DateTime types so that when you reopen it the data displays as either Time or DateTime. If you attempt to open these .TAB files using a non-MapInfo application, these fields display as character fields.

Time and DateTime Data Type Support for Remote Databases

The Time and DateTime data types (see Using Date- and Time-Based Data in Maps and Queries) ensure that the server DATE, TIME, and DATETIME/TIMESTAMP match the MI Date, MI Time, and MI DateTime.

Handling Time and DateTime Data From Remote Databases

When you download Date, Time, and DateTime data type information to MapInfo Pro, this is the results you can expect:

*Backward compatibility depends upon the .tab file version as described above.

When you commit table data from MapInfo Pro to these remote database servers, MapInfo Pro updates the server table with the value that the user provides in the browser. MapInfo Pro may apply some restrictions during the input time.

Working with Time and DateTime Data on the Server

When you create a new table or save a copy of a table with Date, Time, and DateTime data type information on a DBMS server, this is the results you can expect:

*The MapInfo Pro data type will be extended on the servers. When the same data returns to MapInfo Pro it becomes the data type identified on the server. This mismatch is caused by a mismatch between the data types in the server and MapInfo Pro.

As you can see from the previous table, there may be conversion issues involved depending on the local type and the type of database server you are communicating with. To make the data type conversion clearer we have added some messages to inform you of the details of the conversion when you are creating a new table or saving a copy of a table data to a remote database. When you use the Create New Table or Save Copy of Table as dialog boxes, red messages display at the bottom of the dialog box to inform you of the Date, Time, and DateTime conversion details.

If you create a new table with Date, Time, and DateTime data using MapBasic statement, the notification is a little different. If you use the Server Create Table statement, keep in mind that the statement only supports the types that are also supported by the server. Therefore, Time type is prohibited from this statement for Oracle, PostGIS, Microsoft SQL Server and Access servers and the Date type is prohibited for Microsoft SQL Server and Access servers. You should replace unsupported types with DateTime to create a table that contains Time information on a column.

If you create a copy using a MapBasic statement and the source table contains Time or Date type columns, these columns will be converted to DATETIME or TIMESTAMP depending on whether the server supports the data types or not and the parameters you pass in. You can control this behavior using the ConvertDateTime clause. If the source table does not contain Time or Date data type, this clause is not operational. If ConvertDateTime is set to ON (which is the default setting), Time or Date type columns will be converted to DATETIME or TIMESTAMP. If ConvertDateTime is set to OFF the conversion is not done and the operation will be cancelled if necessary. If ConvertDateTime is set to INTERACTIVE a dialog box will pop up to prompt the user and the operation will depend on the user's choice. If the user chooses to convert, then the operation will convert and continue; if the user chooses to cancel, the operation will be cancelled. The Time type requires conversion for all supported servers (Oracle, PostGIS, Microsoft SQL Server and Access) and the Date type requires conversion for Microsoft SQL Server and Access database servers.

Interpreting ASCII and CSV Data

When you register ASCII or CSV files, MapInfo Pro does not automatically recognize fields as being of type Date, Time, or DateTime. In fact, we do not recognize Date, Time or DateTime data for ASCII and CSV files. However, when you export a MapInfo table to these formats, MapInfo Pro writes out the Date, Time, and DateTime fields in their numeric formats.