MATLAB: Incorrect date/time pulled in Matlab MySQL query

MySQLquerytimestamp

I have a database on a remote server that is storing a mysql timestamp. This value is created from an epoch timestamp. When I run a query directly on the server through the command line it returns these fields as follows (mysql timestamp on the left / epoch timestamp on the right):

2020-09-07 17:02:14 | 1599498134 |

Note this result is from a simple query (select * from table where id = value)

When I run the same query through Matlab it returns:

'2020-09-07 11:02:14.0' | 1599498134 |

You will notice the hours have been shifted and there is now a '.0' added at the end. It seems to be auto-adjusting to local time for some reason (the epoch time is in GMT). The query is run through the 'DATA = select(connection, statement);' syntax. Any assistance on how to resolve this is appreciated.

I am using Matlab R2016b

Best Answer

You can either configure the MySQL server to the required timezone.

Also you can try out something like the following command to set driver parameters for timezone in MATLAB

>> conn = database('',<username>,<password>,'com.mysql.cj.jdbc.Driver','jdbc:mysql://servername:portnumber/databasename?serverTimezone=UTC&')

Related Solutions

MATLAB: Does fetch command for sql query fail but runsqlscript work

The answer is that there is a bug in MATLAB 2015b. In order to get this to work use set(conn, 'AutoCommit', 'off').

http://www.mathworks.com/help/database/ug/set.html?refresh=true

MATLAB: How to generate an SQL query that contains dates with Database Toolbox

Different databases require you to specify dates in different formats. Some databases accept dates in a standard string format ('19-Jan-2002') while others require special characters in the string to identify the string as a date.

In general, consult your database documentation for the proper format for dates in SQL queries. Below is some specific information for a couple of databases:

Microsoft Access:

With Microsoft Access, use a # character before and after the date string. Below is an example, where "conn" is a database object in MATLAB as returned by the DATABASE command. The table name is "myDates", and the column name is "DateJoined". In order to select all records where the DateJoined field is after 3/5/05, use the following:

curs = exec(conn,'select * from myDates where DateJoined > #03/05/2005#');
d = fetch(curs);
data = d.Data

Oracle:

With Oracle databases, the date string must be preceded by a data type identifier and enclosed in brackets. Below is an example, where "conn" is a database object in MATLAB as returned by the DATABASE command. The table name is "myDates", and the column name is "DateJoined". In order to select all records where the DateJoined field is after 3/5/05, use one of the following examples. The first statement uses the TIMESTAMP data type:

curs = exec(conn,'select * from myDates where DateJoined > {ts ''2005-03-05 00:00:00''}');
d = fetch(curs);
data = d.Data

The second statement uses the DATE data type:

curs = exec(conn,'select * from myDates where DateJoined > {d ''2005-03-05''}');
d = fetch(curs);
data = d.Data

Best Answer

Related Solutions

MATLAB: Does fetch command for sql query fail but runsqlscript work

MATLAB: How to generate an SQL query that contains dates with Database Toolbox

Related Question