4
$\begingroup$

I'm connecting to a PostgreSQL database, with tables containing TIMESTAMPs with timezone, using

OpenSQLConnection[JDBC["PostgreSQL", "localhost/mydb"], "Username" -> "username", "Password" -> "password"]

and wonder if it is possible to specify that GMT should be used for the transactions?

In Java I always do

TimeZone.setDefault(TimeZone.getTimeZone("GMT"));

before opening the connection.

The issue I have is that when I query the database like

(x = SQLExecute[conn,"SELECT * FROM my_schema.my_tab WHERE epoch>='2003-03-30 00:57:00+00' ORDER BY t LIMIT 7"]) // TableForm

I get

SQLDateTime[{2003, 3, 30, 1, 57, 0.}]  17312.  -1378.  46130.
SQLDateTime[{2003, 3, 30, 1, 58, 0.}]  17312.  -1378.  46129.
SQLDateTime[{2003, 3, 30, 1, 59, 0.}]  17310.  -1377.  46129.
SQLDateTime[{2003, 3, 30, 3, 0, 0.}]   17309.  -1377.  46128.
SQLDateTime[{2003, 3, 30, 3, 1, 0.}]   17308.  -1376.  46127.
SQLDateTime[{2003, 3, 30, 3, 2, 0.}]   17307.  -1376.  46126.
SQLDateTime[{2003, 3, 30, 3, 3, 0.}]   17305.  -1375.  46125.

which are the timestamps in my timezone (GMT+1) and some with daylight savings. This would be fine if SQLDateTime also contained the TimeZone and daylight savings, but it just contains a List in the same way as DateList does.

This would be solved if the timestamps from/to the database were always in GMT.

I'm quite new to Mathematica so maybe there is a simple solution.

Update 2015-02-14:

This is not an answer to the original question but it is a solution that works at the moment. It is also somewhat similar to this post.

In PostgreSQL I define the function

create function math_epoch(timestamp with time zone)
returns float
as 'select extract(epoch from $1)+2208988800;'
language sql immutable returns null on null input;

which returns the number of seconds since 1900-01-01 00:00:00+00. For example

select math_epoch('1900-01-01 00:00:00+00');
math_epoch 
------------
          0
(1 row)

select math_epoch('1970-01-01 00:00:00+00');
math_epoch 
------------
 2208988800
(1 row)

Then I read data with the function

(x = SQLExecute[conn,"SELECT math_epoch(t),x,y,z FROM my_schema.my_tab WHERE epoch>='2003-03-30 00:57:00+00' ORDER BY t LIMIT 7"]) // TableForm

3.25797*10^9  17312.  -1378.  46130.
3.25797*10^9  17312.  -1378.  46129.
3.25797*10^9  17310.  -1377.  46129.
3.25797*10^9  17309.  -1377.  46128.
3.25797*10^9  17308.  -1376.  46127.
3.25797*10^9  17307.  -1376.  46126.
3.25797*10^9  17305.  -1375.  46125.

DateList can now convert this using the current timezone

DateList[#[[1]]] & /@ x // TableForm

2003  3  30  0  57  0.
2003  3  30  0  58  0.
2003  3  30  0  59  0.
2003  3  30  1   0  0.
2003  3  30  1   1  0.
2003  3  30  1   2  0.
2003  3  30  1   3  0.

This works because the number of seconds in Mathematica is relative to 1900-01-01 00:00:00 in the current timezone.

There is a performance penalty with the math_epoch() function, I don´t know large it is, but a simple test in psql gives this:

select count(t) from my_schema.my_table;
  count   
----------
 14201280
(1 row)

Time: 13241.557 ms

select count(math_epoch(t)) from my_schema.my_table;
   count   
 ----------
  14201280
 (1 row)

 Time: 66438.799 ms

A solution to the original question would be better, but for now it works. Any feedback or comments that can improve the solution are welcome.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.