Changing time zone after getting GMT time from database

[Majid Azimi]

Hi guys,

I have MonetDB database with table containing user table:

CREATE TABLE user
(
    birth_date TIMESTAMP NOT NULL
);

birth_date is saved in GMT without DST. (This is the
 default behavior of MonetDB). 
So I should change the TimeZone in my application. Here is my code:

Class.forName("nl.cwi.monetdb.jdbc.MonetDriver");
Connection con = DriverManager.getConnection("jdbc:monetdb://localhost/online", "monetdb", "monetdb");      
Statement st = con.createStatement();
ResultSet rs;

Calendar c = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
rs = st.executeQuery("SELECT * FROM user");
while (rs.next()) {
    c.setTime(rs.getTimestamp("birth_date"));
   c.setTimeZone(TimeZone.getTimeZone("Asia/Tehran"));
    System.out.println(c.get(Calendar.YEAR) + "-" + c.get(Calendar.MONTH) + "-" + c.get(Calendar.DAY_OF_MONTH) + " " + c.get(Calendar.HOUR_OF_DAY) + ":" + c.get(Calendar.MINUTE)
 + ":" + c.get(Calendar.SECOND));
}

But there is no difference between timestamps in database
 
and timestamps after converting time zone in application.
Could anyone help?
Is this the wrong way to convert TimeZone? 
I'm using MonetDB version 11.9.5-20120516 on Debian 6 with openjdk 6. 
Here is the monetdbd getall /home/dbfarm:

dbfarm           /home/dbfarm/
status           monetdbd[4187] 1.6 (Apr2012-SP1) is serving
 this dbfarm
mserver          /usr/bin/mserver5
logfile          /home/dbfarm//merovingian.log
pidfile          /home/dbfarm//merovingian.pid
sockdir          /tmp
port             50000
exittimeout    
  60
forward          proxy
discovery        yes
discoveryttl     600
control          yes
passphrase       {SHA512}ba3253876aed6bc22d4a6ff53d8406c6ad864195ed144ab5c87621b6c233b548baeae6956df346ec8c17f5ea10f35ee3cbc514797ed7ddd3145464e2a0bab413
mapisock         /tmp/.s.monetdb.50000
controlsock      /tmp/.s.merovingian.50000

------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
MonetDB-users mailing list
MonetDB-users@...
https://lists.sourceforge.net/lists/listinfo/monetdb-users

[Fabian Groffen]

Hi Majid,

On 27-05-2012 05:37:32 -0700, Majid Azimi wrote:
[snip]
> But there is no difference between timestamps in database
>
> and timestamps after converting time zone in application.
> Could anyone help?
> Is this the wrong way to convert TimeZone?

I'm not sure I understand what you want.  The example code you showed
looked daft, since you seem to be changing timezones on the fly.

The Driver accomodates for your timezone, that is, the timezone that's
default in your JVM when it makes a connection to the database.  If you
want to use another timezone for timestamps/times/dates than the default
of your JVM, then set the desired timezone before creating a new
connection to the database (DriverManager.getConnection()).

If you have a scenario where many timezones are involved in retrieving
and storing timestamps, you probably best use TIMESTAMP WITH TIMEZONE
instead to save yourself a lot of trouble.

Fabian


------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
MonetDB-users mailing list
MonetDB-users@...
https://lists.sourceforge.net/lists/listinfo/monetdb-users