This is a reference for the date and time contexts and formats you can use with ThoughtSpot. You define data formats in specific contexts and, depending on the context, your choices in data formatting differ. You must understand date and time when you load data in these contexts:
- using data upload from the browser
- through
tsload
command - through an extract, transform, load (ETL) tool
Data loading formats do not change how data is displayed in tables and charts.
The context where you can control date and time formats is data modeling. Data modeling controls how data is displayed in search and their resulting answers.
Data loading formats through tsload
When loading through the tsload
command you must specify date
and timestamp
formats using the format specifications defined in the strptime
library function. Data is
imported based on the timezone of the node from which tsload
is run.
For date
data types, the default format is %Y%m%d
, which translates to
yearmonthday
For example, Dec 30th, 2001
is represented as 20011230
. For
time
and datetime
data types, the default is %Y%m%d %H:%M:%S
which
translates to yearmonthday hour:minute:second
, for example, Dec 30th, 2001
1:15:12
is represented as 20011230 01:15:12
.
Data modeling formats for browser data upload
These date and time formats are supported in a CSV file when uploading through the browser. You cannot specify the date format. If you are creating a new table, ThoughtSpot picks the format that fits your data best. If you are updating an existing table, ThoughtSpot uses the format that table already has.
- 1/30/2014
- 2014-01-30
- 2014-1-30
- 30-Jan-2014
- 2014-Jan-30
- 2014-01-30 10:32 AM
- 2014-01-30 14:52
- 2014-01-30 10:32:22
- 2014-01-30 10:32:22 AM
- 2014-01-30 10:32:22.0
- 2014-01-30 10:32:22.0 AM
- 2014-01-30 10:32:22.000
- 2014-01-30 10:32:22.000 AM
- 1/30/2014
- 30-Jan-14
- 01-Mar-02 (assumes 2002)
- 30/1/2014 10:32 AM
- 30/1/2014 14:52
- 30/1/2014 10:32:22
- 30/1/2014 10:32:22 AM
- 30/1/2014 10:32:22.0
- 30/1/2014 10:32:22.0 AM
- 30/1/2014 10:32:22.000
- 30/1/2014 10:32:22.000 AM
- 30-Jan-14 10:32 AM
- 30-Jan-14 14:52
- 30-Jan-14 10:32:22
- 30-Jan-14 10:32:22 AM
- 30-Jan-14 10:32:22.0
- 30-Jan-14 10:32:22.0 AM
- 30-Jan-14 10:32:22.000
- 30-Jan-14 10:32:22.000 AM
- Fri Jan 30 2014 3:26 PM
- Fri Jan 30 2014 13:46
- Fri Jan 30 2014 10:32:22
- Fri Jan 30 2014 10:32:22 AM
- Fri Jan 30 2014 10:32:22.0
- Fri Jan 30 2014 10:32:22.0 AM
- Fri Jan 30 2014 10:32:22.000
- Fri Jan 30 2014 10:32:22.000 AM
- 14:52
- 10:32 AM
- 10:32:22
- 10:32:22 AM
- 10:32:22.0
- 10:32:22.000
- 10:32:22.0 AM
- 10:32:22.000 AM
Data loading formats through an ETL tool
Data loads through ETL uses ODBC or JDBC connections. After you extract the data from the source but before you load it into ThoughtSpot, you must transform any date or timestamp columns into a format that is valid for ThoughtSpot. After the data transformation completes, there is no requirement for explicit data masking. See the data integration guide for more information on loading data through ODBC and JDBC.
Data modeling formats
A user with administrative rights can configure data modeling for data on one or all files. You can set number, date, and currency display formats. These formats define how these value types display in tables and charts. See the Admin Guide for more information about data modeling settings. The following format strings are available for use:
Format mask | Description |
---|---|
YYYY or yyyy |
four digit year such as 2017 |
YY or yy |
last two digits of year such as 17 |
M |
month with no leading zero 1 -12 |
MM |
Two digit month 01 -12 |
MMM |
Three letter month such as Jan |
D |
Day of year without a leading zero 0 -365 |
DD |
Day of year with up to one leading zero 01 -365 |
DDD |
Day of year with up to two leading zeroes 001 -365 |
d |
Day of month with no leading zero 1 -31 |
dd |
Two digit day of month 01 -31 |
HH |
Two digit 24 hour representation of hour 00 -23 |
hh |
Two digit 12 hour representation of hour 01 -12 |
H |
24 hour representation of hour with no leading zero 0 -23 |
h |
12 hour representation of hour with no leading zero 1 -12 |
mm |
Minutes 00 -59 |
m |
Minutes with no leading zero 0 -59 |
ss |
Seconds 00 -59 |
s |
Seconds with no leading zero 0 -59 |
a |
AM/PM indicator |
Valid delimiters include most non-alphabet characters. This includes but is not limited to:
/
(forward slash)\
(backward slash)|
(pipe symbol):
(colon)-
(dash)_
(underscore)=
(equal sign)
Examples of valid format masks you can produce for display are as follows:
MM/dd/yyyy
MMM
DD/MM/yyyy
MM/dd/yyyy HH:mm
DD/MM/yyyy HH:mm