001/* 002 * Copyright (c) 2018-2023 Tada AB and other contributors, as listed below. 003 * 004 * All rights reserved. This program and the accompanying materials 005 * are made available under the terms of the The BSD 3-Clause License 006 * which accompanies this distribution, and is available at 007 * http://opensource.org/licenses/BSD-3-Clause 008 * 009 * Contributors: 010 * Chapman Flack 011 */ 012package org.postgresql.pljava.example.annotation; 013 014import java.sql.Connection; 015import java.sql.Date; 016import java.sql.DriverManager; 017import java.sql.Statement; 018import java.sql.ResultSet; 019import java.sql.Savepoint; 020import java.sql.SQLException; 021 022import static java.util.logging.Logger.getAnonymousLogger; 023import java.util.TimeZone; 024 025import org.postgresql.pljava.annotation.Function; 026import org.postgresql.pljava.annotation.SQLAction; 027 028/** 029 * Some tests of pre-JSR 310 date/time/timestamp conversions. 030 *<p> 031 * For now, just {@code java.sql.Date}, thanks to issue #199. 032 */ 033@SQLAction(provides="language java_tzset", install={ 034 "SELECT sqlj.alias_java_language('java_tzset', true)" 035}, remove={ 036 "DROP LANGUAGE java_tzset" 037}) 038 039@SQLAction( 040 requires="issue199", install={ 041 "SELECT javatest.issue199()" 042}) 043public class PreJSR310 044{ 045 private static final String TZPRAGUE = "Europe/Prague"; 046 047 static 048 { 049 TimeZone oldZone = TimeZone.getDefault(); 050 TimeZone tzPrague = TimeZone.getTimeZone(TZPRAGUE); 051 052 try 053 { 054 TimeZone.setDefault(tzPrague); 055 } 056 finally 057 { 058 TimeZone.setDefault(oldZone); 059 } 060 } 061 062 /** 063 * Test for a regression in PG date to/from java.sql.Date conversion 064 * identified in issue #199. 065 *<p> 066 * Checks that two months of consecutive dates in October/November 2018 067 * are converted correctly in the Europe/Prague timezone. The actual issue 068 * was by no means limited to that timezone, but this test reproducibly 069 * detects it. 070 *<p> 071 * This function is defined in the 'alias' language {@code java_tzset}, for 072 * which there is an entry in the default {@code pljava.policy} granting 073 * permission to adjust the time zone, which is temporarily done here. 074 */ 075 @Function( 076 schema="javatest", language="java_tzset", 077 requires="language java_tzset", provides="issue199" 078 ) 079 public static void issue199() throws SQLException 080 { 081 TimeZone oldZone = TimeZone.getDefault(); 082 TimeZone tzPrague = TimeZone.getTimeZone(TZPRAGUE); 083 Connection c = DriverManager.getConnection("jdbc:default:connection"); 084 Statement s = c.createStatement(); 085 Savepoint svpt = c.setSavepoint(); 086 boolean ok = true; 087 try 088 { 089 TimeZone.setDefault(tzPrague); 090 s.execute("SET LOCAL TIME ZONE '" + TZPRAGUE + "'"); 091 092 ResultSet rs = s.executeQuery( 093 "SELECT" + 094 " d, to_char(d, 'YYYY-MM-DD')" + 095 " FROM" + 096 " generate_series(0, 60) AS s(i)," + 097 " LATERAL (SELECT date '2018-10-01' + i) AS t(d)"); 098 while ( rs.next() ) 099 { 100 Date dd = rs.getDate(1); 101 String ds = rs.getString(2); 102 if ( ! ds.equals(dd.toString()) ) 103 ok = false; 104 } 105 } 106 finally 107 { 108 TimeZone.setDefault(oldZone); 109 c.rollback(svpt); // restore prior PG timezone 110 s.close(); 111 c.close(); 112 } 113 114 if ( ok ) 115 getAnonymousLogger().info("issue 199 test ok"); 116 else 117 getAnonymousLogger().warning("issue 199 test not ok"); 118 } 119}