Supported data types
MariaDB / MySQL
The following data types are supported as user report parameters and in the report's select columns.
| Numeric | Date and time | Character |
|---|---|---|
| tinyint | timestamp | char |
| smallint | date | varchar |
| int | time | tinyblob (*) |
| mediumint | datetime | mediumblob (*) |
| bigint | longblob (*) | |
| float | blob (*) | |
| double | tinytext | |
| decimal | mediumtext | |
| year | longtext | |
| text | ||
| json |
Time data type can be used as normal time or as a duration.
When used as regular time, the column is formatted based on the format defined in the preferences. Normal time has a range of 00:00:00 - 23:59:59.
If you apply a style '%s' to a time column or use aggregate function sum or avg, the time column is then treated as duration, and the format is always H:M:S.
(*) For text data
Microsoft SQL Server
| Numeric | Date and time | Character |
|---|---|---|
| bit | smalldatetime | char |
| tinyint | datetime | nchar |
| smallint | date (*) | varchar |
| int | time (*) | nvarchar |
| bigint | datetime2 (*) | text |
| float | datetimeoffset (*) | ntext |
| decimal | sysname | |
| numeric | ||
| real | ||
| smallmoney | ||
| money |
(*) For SQL Server 2008 and newer.
Note that older TDS protocol versions do report not identify date data types correctly. Use ':type=date' notation in column name to specify the correct data type.
declare @d date
declare @dt datetime2
declare @time time
declare @datetimeoffset datetimeoffset
select @d = getdate(), @dt = getdate(), @time = getdate(), @datetimeoffset = getdate()
select @d as 'date:type=date',
@dt as 'datetime2:type=datetime',
@time as 'time:type=time',
@datetimeoffset as 'datetimeoffset:type=datetime'
PostgreSQL
| Numeric | Date and time | Character |
|---|---|---|
| smallint | date | char |
| integer | time, timetz | varchar |
| bigint | timestamp | text |
| numeric | timestamptz | citext |
| real | uuid | |
| double precision | xml | |
| money | json, jsonb | |
| boolean (shown as 1 / 0) | bytea (binary) |
Arrays, geometric types and interval are shown as text. Cast them to the type you want in the query.
All values are returned as strings by the PostgreSQL driver and are typed by myDBR from the column's data type, so date and time columns are formatted with the preferences. Use double quoted aliases to keep the case and the special characters of the column heading, for example d as "Order date:type=date".