Common fixes that miss the problem
The quantity crossing the client boundary is time represented as UTC milliseconds. Ignition carries a database datetime through the system as a java.util.Date object, but the browser cannot receive that Java object directly. Perspective therefore transmits the underlying millisecond value, and the Table must be told to render that value as a date.
Several familiar corrections act at the wrong layer:
| Attempted fix | Why it fails or creates a new problem | Correct layer |
|---|---|---|
| Changing the database column to text | It discards the native datetime type, complicates sorting and filtering, and couples storage to one display format. | Perspective Table column rendering |
| Formatting the entire query result as strings | It may make the cell readable, but the Table no longer receives a date value. Date-aware operations then become string operations. | .props.columns |
| Applying one renderer to a mixed-type table | Each column has its own data type and display requirement. A table-wide conversion can damage numeric, Boolean, or text behavior. | Individual column configuration |
| Adding or subtracting hours from the stored value | The visible integer is a representation issue, not proof of a timezone error. An offset changes the instant instead of changing its presentation. | Date renderer and the intended display timezone |
| Building a browser-side string conversion first | Perspective components already know how to turn transmitted milliseconds into date/time text when configured for that role. | Built-in Table date rendering |
Timestamp representation and rendering
The number that matters is the millisecond timestamp. A java.util.Date identifies an instant using milliseconds under the hood; it does not carry a presentation pattern such as year-month-day or month/day/year. The database, Gateway, and Perspective session can therefore preserve the instant while displaying it differently at each boundary.
A browser uses JavaScript rather than Java and has no java.util.Date object. Perspective serializes the instant for transport and sends the underlying UTC milliseconds to the client. If the Table treats that payload as an ordinary number or uses an unconfigured column, the cell can expose the numeric representation. When the column is configured as a date, the component converts the same value into date/time text for display.
This separation is intentional: storage retains a sortable datetime value, transport retains the instant, and presentation selects the visible pattern. Converting the value to a string early collapses those three responsibilities into one and makes later sorting, filtering, localization, and timezone handling harder.
Column configuration decision points
Configure every displayed field explicitly in the Table's .props.columns list. Match the configured column to the corresponding field in the table data, then assign date-aware rendering to the datetime field. Leave numeric, Boolean, and text fields on renderers appropriate to their own types.
| Quantity or setting | Expected condition | Where to read or set it |
|---|---|---|
| Source value type | Native database datetime carried into Ignition as java.util.Date
|
Database query result and binding preview |
| Transport representation | UTC milliseconds sent to the Perspective client | Perspective session data flow; this is normally handled by the component |
| Column-to-field mapping | The column entry targets the datetime field, including matching spelling and case | Table .props.columns
|
| Column rendering | Date/time rendering rather than plain numeric or text rendering | The datetime column entry in .props.columns
|
| Visible format | The project-required date or date/time pattern | Date rendering options in the column configuration |
| Displayed timezone | The intended session or project timezone behavior | Compare the rendered cell with the known database instant and session context |
The official property reference is Perspective Table Properties. The individual entries and rendering choices are described in Table Column Configurations.
Working configuration procedure
- Preserve the database datatype. Return the field as a datetime value rather than converting it to formatted text in the query. This keeps the instant available to the Table as a date-capable value.
- Inspect the binding result. Identify the exact field that contains the datetime. If the dataset or array contains multiple data types, record which fields are dates and which are numbers, strings, or Booleans.
-
Open the Table column list. Edit
.props.columnsand create or select the entry corresponding to the datetime field. Explicit column configuration prevents the mixed dataset from relying on a generic renderer. - Match the field mapping. Point that column entry at the datetime field. A renderer attached to the wrong field leaves the actual date column unchanged.
- Select date-aware rendering. Configure the column to interpret and display its value as a date or date/time. Choose the visible pattern required by the operators, such as date only or date plus time, using the options exposed by the Table column configuration.
- Configure the remaining columns independently. Retain number rendering for numeric process values and the corresponding renderers for other types. This isolates presentation changes to the datetime column.
- Save and exercise the Perspective view. Test through a running session, because the Gateway-to-browser serialization and client rendering occur in the session path.
Verification by value and behavior
Start with a row whose database timestamp is known. Compare the database instant with the rendered cell, accounting for the intended timezone presentation. A readable date alone is insufficient: a cell can have the desired shape while representing the wrong instant.
- Confirm that the large millisecond number no longer appears in the datetime column.
- Confirm that null datetime values remain empty or follow the project's intended null presentation rather than producing a misleading epoch date.
- Compare at least one value near a date boundary. A timezone mismatch often changes the calendar date even when ordinary daytime samples look plausible.
- Sort the column and check that results follow chronological order. Lexical ordering after an early string conversion may produce a different sequence.
- Filter or select rows through the normal operator workflow and confirm that downstream actions still receive the expected underlying value type.
- Test each datetime column separately when the table contains more than one; each entry requires its own mapping and rendering configuration.
If the display pattern is correct but the hour is wrong, stop changing the pattern. Trace the instant from the database result to the session and identify which timezone should control presentation. Formatting changes character layout; timezone conversion changes the displayed clock time.
Recurring implementation pitfalls
A field alias that differs from the configured column mapping is a common reason for an apparently correct renderer to have no effect. Compare the binding's field names directly with the entries in .props.columns, including case.
Another failure mode is double formatting. A query or script first converts the datetime to text, then the Table is configured to render that text as a date. The renderer no longer receives the native date value it expects. Keep the value typed as a datetime until the presentation layer unless an external interface explicitly requires a string.
Timezone corrections also become duplicated easily. If one layer changes the instant and another layer applies the session's timezone presentation, the visible value can shift twice. Establish one authoritative stored instant and verify the display against it before adding transformations.
Client-side string generation remains useful when exporting a prescribed textual format or feeding an interface that accepts only text. For ordinary Table cells, it adds parsing rules, null handling, localization behavior, and sorting consequences that the built-in date renderer already addresses.
FAQ
Why does an Ignition Perspective Table show a date as a large number?
Perspective transmits the underlying UTC milliseconds because a browser cannot receive Ignition's java.util.Date object directly. Configure the matching entry in .props.columns for date/time rendering.
Why does formatting the date in SQL cause sorting problems?
SQL-side formatting usually turns the datetime into text, so the Table may sort characters rather than chronological values. Return the native datetime and apply the visible format in the Table column configuration.
Why is the formatted Perspective date showing the wrong day or hour?
The format controls layout, while timezone handling controls the displayed clock value. Compare a known database instant with the running session, especially near a date boundary, before applying any offset.
When should I escalate a Perspective Table date issue?
Stop when the query returns a native datetime, the correct field is mapped in .props.columns, date rendering is selected, and a known instant still renders incorrectly in a live session. Record the database value, binding result, column configuration, intended timezone, and observed session output, then escalate through Inductive Automation's official support channel. Avoid further timestamp arithmetic because it can conceal the serialization or timezone boundary that support needs to diagnose.