Date and time formats reference

ThoughtSpot supports several date and time formats.

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:

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

03/15/2021
MMM

Mar
DD/MM/yyyy

15/03/2021
MM/dd/yyyy HH:mm

03/15/2021 12:05
DD/MM/yyyy HH:mm

15/03/2021 12:05
Was this page helpful?
×