Time Zones in DolphinDB

Context

• The stock records from a New York-based exchange are stored in a database. A column contains the trade time, e.g., 2022.02.20 09:35:03.
• The database is deployed in Beijing (UTC+8).
• Some of the client applications that consume the data are deployed in Beijing and the others are deployed in the San Francisco (UTC-8).

Business Requirements

All users, whether in Beijing or San Francisco, must see the trade data in the exchange's local time (UTC-5). In this section, we will use the trade time of a single record, 2022.02.20 09:35:03, as an example.

In some scenarios, time zone information must be taken into consideration. DolphinDB also provides built-in functions for time zone conversion.

Context

• An online deal was made between a Chinese buyer and a Japanese vendor at 2022.05.20 11:30:05 Beijing Time (UTC +8), which is 2022.05.20 12:30:05 Tokyo Time (UTC+9).
• The e-commerce company has two data centers in China and Japan, which store transaction data from China and Japan, respectively.

Business Requirements

When users browse their orders, the transaction time must be displayed in their current time zone, i.e., the Chinese buyer sees the transaction time in Beijing Time and the Japanese vendor sees it in Tokyo time.

Users who are not familiar with how the Date class is stored and printed in Java may find the temporal values "inconsistent" before and after the data import to DolphinDB.

1. Instantiate the Date class

   Date date = new Date();
   System.out.println(date);

   Note that while the date object is printed in system time format, it is stored as a Unix timestamp (of the LONG type).

2. Import data to DolphinDB

(1) Execute the script below to create a shared in-memory table with two columns in DolphinDB:

conn.run("t = table(100:0,`ts`value,[TIMESTAMP,INT]);share t as timeJava;");

(2) Insert a record to the table through Java API:

public static void write() throws IOException {
    DBConnection conn = new DBConnection();
    conn.connect("localhost", 8848, "admin", "123456");
    LinkedList<Long> dtList = new LinkedList<>();// ts
    LinkedList<Integer> valueList = new LinkedList<>();// value
    Date date = new Date();
    System.out.println(date);
    dtList.add(date.getTime());
    valueList.add(1);
    List<Entity> data = Arrays.asList(
    new BasicTimestampVector(dtList),
    new BasicIntVector(valueList)
    );
    conn.run("tableInsert{\"testJava\"}", data);
}

(3) Print the date object which is inserted to the DolphinDB table to your console with System.out.println(date). Meanwhile, check the inserted record in DolphinDB GUI. Compare the two temporal values:

The two values do not match. The reason is as follows:

The Date object is instantiated from the Unix timestamp returned by System.currentTimeMillis(). For more information about the constructor of Date, see official documentation.

/**
* Allocates a <code>Date</code> object and initializes it so that
* it represents the time at which it was allocated, measured to the
* nearest millisecond.
*
* @see     java.lang.System#currentTimeMillis()
*/
public Date() {
	this(System.currentTimeMillis());
}

In Java, the string representation (printed output) of the Date class is customized by overriding its toString method which involves time zone conversion (see source code below). Therefore, although Date is stored as a Unix timestamp, it is printed based on the time zone of the server.

public String toString() {
        // "EEE MMM dd HH:mm:ss zzz yyyy";
        BaseCalendar.Date date = normalize();
        StringBuilder sb = new StringBuilder(28);
        int index = date.getDayOfWeek();
        if (index == BaseCalendar.SUNDAY) {
            index = 8;
        }
        convertToAbbr(sb, wtb[index]).append(' ');                        // EEE
        convertToAbbr(sb, wtb[date.getMonth() - 1 + 2 + 7]).append(' ');  // MMM
        CalendarUtils.sprintf0d(sb, date.getDayOfMonth(), 2).append(' '); // dd
        CalendarUtils.sprintf0d(sb, date.getHours(), 2).append(':');   // HH
        CalendarUtils.sprintf0d(sb, date.getMinutes(), 2).append(':'); // mm
        CalendarUtils.sprintf0d(sb, date.getSeconds(), 2).append(' '); // ss
        TimeZone zi = date.getZone();
        if (zi != null) {
            sb.append(zi.getDisplayName(date.isDaylightTime(), TimeZone.SHORT, Locale.US)); // zzz
        } else {
            sb.append("GMT");
        }
        sb.append(' ').append(date.getYear());  // yyyy
        return sb.toString();
}

DolphinDB, on the other hand, stores the Unix timestamp of the imported time in the LONG type and displays it in the UTC time zone.

Therefore, although temporal values appear "inconsistent" before and after the import to DolphinDB, nothing is in fact modified during the process.

This section discusses these temporal data types in MySQL 5.7: DATETIME, TIMESTAMP, DATE and TIME.

• MySQL converts TIMESTAMP values from the current time zone to Unix timestamps for storage, and back from Unix timestamps to the current time zone for retrieval.
• Such conversion does not occur for DATE, TIME or DATETIME.

Use the command below to check the values of the time zone variables in MySQL. The output shows that the system time zone is "CST" (China Standard Time, i.e., UTC+8) and the server time zone is the same as the system time zone.

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+

Execute the following commands to create a database and a table in MySQL:

mysql> USE testdb;
mysql> CREATE TABLE testTable(
    -> date DATE NOT NULL,
    -> time TIME NOT NULL,
    -> ts TIMESTAMP NOT NULL
    -> );
Query OK, 0 rows affected (0.11 sec)

Insert a record. Check the data in the table:

mysql> select * from testTable;
+------------+----------+---------------------+
| date       | time     | ts                  |
+------------+----------+---------------------+
| 2022-06-14 | 23:13:15 | 2022-06-14 23:13:15 |
+------------+----------+---------------------+
1 row in set (0.00 sec)

Specify the server time zone to UTC +9 and check the table again:

mysql> set time_zone='+09:00';
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | +09:00 |
+------------------+--------+
2 rows in set (0.00 sec)

mysql> select * from testTable;
+------------+----------+---------------------+
| date       | time     | ts                  |
+------------+----------+---------------------+
| 2022-06-14 | 23:13:15 | 2022-06-15 00:13:15 |
+------------+----------+---------------------+
1 row in set (0.00 sec)

Compare the query results before and after the time zone change: the "date" and "time" columns contain fixed DATE and TIME values and therefore dose not change; the TIMESTAMP values in the "ts" column go forward 1 hour as the time zone changed from UTC+8 to UTC+9.

The DolphinDB MySQL plugin allows you to smoothly import data from MySQL into DolphinDB with automatic data type conversion. The supported temporal data types are as follows:

DolphinDB also has the following additional temporal data types: MONTH, MINUTE, SECOND, NANOTIME, NANOTIMESTAMP

First, import a CSV file containing NYSE stock data into MySQL. The global time_zone value is set to UTC.

The CSV file and the script for importing it into MySQL can be downloaded in the Appendix. To better compare the DATE, TIME and TIMESTAMP values in the result, a "ts" column (of the TIMESTAMP type) containing the Unix timestamps (which are 5 hours ahead of the exchange's local time) for the trade is added to the table.

Check the imported data in MySQL:

mysql> select * from taqTs limit 5;
+--------+------------+----------+-------+-------+--------+--------+------+-----+------+---------------------+
| symbol | date       | time     | bid   | ofr   | bidsiz | ofrsiz | mode | ex  | mmid | ts                  |
+--------+------------+----------+-------+-------+--------+--------+------+-----+------+---------------------+
| A      | 2022-02-20 | 06:24:34 |  1.00 |  0.00 |      1 |      0 |   12 | 'P' | null | 2022-02-20 11:24:34 |
| A      | 2022-02-20 | 06:24:56 |  0.00 |  0.00 |      0 |      0 |   12 | 'P' | null | 2022-02-20 11:24:56 |
| A      | 2022-02-20 | 07:02:00 |  0.00 | 54.84 |      0 |      1 |   12 | 'T' | null | 2022-02-20 12:02:00 |
| A      | 2022-02-20 | 07:02:00 | 22.17 | 54.84 |      1 |      1 |   12 | 'T' | null | 2022-02-20 12:02:00 |
| A      | 2022-02-20 | 07:08:22 | 37.00 | 54.84 |      1 |      1 |   12 | 'T' | null | 2022-02-20 12:08:22 |
+--------+------------+----------+-------+-------+--------+--------+------+-----+------+---------------------+

Load the MySQL plugin in DolphinDB and import the table from MySQL into DolphinDB as an in-memory table.

// load plugin
loadPlugin("/DolphinDB/server/plugins/mysql/PluginMySQL.txt");

// connect to MySQL Server
conn = mysql::connect(`127.0.0.1, 3306, `root, "root", `testdb)

// import data
taqDdb = mysql::load(conn, "SELECT * FROM taqTs");

// view imported data
select * from taqDdb;

The temporal values in the result are consistent with the DATE, TIME and TIMESTAMP values in MySQL. Therefore, to import Unix timestamps of the TIMESTAMP type from MySQL to DolphinDB, make sure that the global time_zone system variable is set to UTC.

Note that in MySQL the time_zone variable can be set at global and session level. DolphinDB stores the imported data from MySQL according to the global time zone. Therefore, if the temporal values are inconsistent before and after the import, check whether your session time zone is different from the global time zone in MySQL.

For example, set time_zone = '+08:00' in a MySQL session and then query the table. The query result shows that in this particular session, the "ts" column values are displayed in UTC +8 time, instead of in UTC time.

mysql> set time_zone='+08:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from taqTs limit 5;
+--------+------------+----------+-------+-------+--------+--------+------+-----+------+---------------------+
| symbol | date       | time     | bid   | ofr   | bidsiz | ofrsiz | mode | ex  | mmid | ts                  |
+--------+------------+----------+-------+-------+--------+--------+------+-----+------+---------------------+
| A      | 2022-02-20 | 06:24:34 |  1.00 |  0.00 |      1 |      0 |   12 | 'P' | null | 2022-02-20 19:24:34 |
| A      | 2022-02-20 | 06:24:56 |  0.00 |  0.00 |      0 |      0 |   12 | 'P' | null | 2022-02-20 19:24:56 |
| A      | 2022-02-20 | 07:02:00 |  0.00 | 54.84 |      0 |      1 |   12 | 'T' | null | 2022-02-20 20:02:00 |
| A      | 2022-02-20 | 07:02:00 | 22.17 | 54.84 |      1 |      1 |   12 | 'T' | null | 2022-02-20 20:02:00 |
| A      | 2022-02-20 | 07:08:22 | 37.00 | 54.84 |      1 |      1 |   12 | 'T' | null | 2022-02-20 20:08:22 |
+--------+------------+----------+-------+-------+--------+--------+------+-----+------+---------------------+
5 rows in set (0.00 sec)

Connect DolphinDB to MySQL again and import the above table. As the MySQL global time zone is still UTC and the session time zone is not specified, the "ts" column in the imported table remain unchanged.

Now, we change the MySQL global time zone to UTC +8 through set global time_zone='+08:00'. Reconnect to MySQL to import the table and check the result in DolphinDB. As the TIMESTAMP values are stored as Unix timestamps and converted back to the current time zone for retrieval in MySQL, the "ts" column values are displayed in UTC+8 time DolphinDB:

With the functions loadText and loadTextEx, you can import CSV files to in-memory tables or DFS tables, respectively. This section uses loadText as an example to describe how to import time from a CSV file.

Note: It is recommended to check the data type of each column with the extractTextSchema function before loading. If a column does not have the expected data type, enter the correct data type of the column in the schema table. For a temporal column, we also need to specify a format such as "MM/dd/yyyy" in the schema table. For more information, see Parsing and Format of Temporal Variables and Importing Text Files Tutorial.

Load the CSV file into DolphinDB:

data = loadText("/data/taq.csv")

Specify the DATE type for the "date" column values and TIME type for the "time" column values. The temporal values will be stored without time zone information.

DolphinDB provides time zone conversion functions for retrieving the temporal values from database. Take the "data" table as an example:

• gmtime - converts local time (UTC+8 in this example) to GMT (UTC+0 zone):

gmData = select gmtime(concatDateTime(date,time)) as utc, * from data;

• localtime - converts GMT (UTC+0 zone) to local time zone (UTC+8 in this example):

localData = select localtime(utc) as localtime,* from gmData; 

• convertTZ - time conversion between time zones

convertTZ(2022.04.25T08:25:45,"US/Eastern","Asia/Shanghai");

DolphinDB stores temporal values without time zone information. Instead, it lets you decide in which time zone you would like to store and retrieve temporal values by offering various time zone conversion functions.