FATAL: invalid value for parameter "TimeZone": "Asia/Saigon"

Question

During backend development for a Java application, I set up a PostgreSQL database connection using JDBC in DatabaseConnection.java. I then ran a simple backend app to test the connection through UserDAO.java. After a successful build, I executed the program but encountered the following error:

"C:\Program Files\Java\jdk-23\bin\java.exe" "-javaagent:C:\Program Files\JetBrains\IntelliJ IDEA 2024.3.2\lib\idea_rt.jar=59302:C:\Program Files\JetBrains\IntelliJ IDEA 2024.3.2\bin" -Dfile.encoding=UTF-8 -Dsun.stdout.encoding=UTF-8 -Dsun.stderr.encoding=UTF-8 -classpath C:\Users\ADMIN\.m2\repository\org\postgresql\postgresql\42.5.0\postgresql-42.5.0.jar;C:\Users\ADMIN\.m2\repository\org\checkerframework\checker-qual\3.5.0\checker-qual-3.5.0.jar -p D:\phdoanh\AniShelf\backend\target\classes -m com.library.backend/com.library.backend.BackendApplication
Starting Backend Application...
org.postgresql.util.PSQLException: FATAL: invalid value for parameter "TimeZone": "Asia/Saigon"
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
    at org.postgresql.core.v3.QueryExecutorImpl.readStartupMessages(QueryExecutorImpl.java:2788)
    at org.postgresql.core.v3.QueryExecutorImpl.<init>(QueryExecutorImpl.java:174)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:290)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
    at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:247)
    at org.postgresql.Driver.makeConnection(Driver.java:434)
    at org.postgresql.Driver.connect(Driver.java:291)
    at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:682)
    at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:230)
    at [email protected]/com.library.backend.dao.DatabaseConnection.getConnection(DatabaseConnection.java:14)
    at [email protected]/com.library.backend.dao.UserDAO.getFirstUserName(UserDAO.java:13)
    at [email protected]/com.library.backend.BackendApplication.main(BackendApplication.java:15)
No users found in the database.
Hello John

Process finished with exit code 0

I checked the timezone directly in my DBMS using the command SHOW timezone;, and it correctly returned Asia/Ho_Chi_Minh - a timezone supported by PostgreSQL. Please share a solution if you're familiar with this issue!

P.S. I'm suspecting the problem might be on the Oracle Cloud Server side, where my DBMS is hosted.

Below are the files I mentioned:

DatabaseConnection.java

package com.library.backend.dao;  
  
import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.SQLException;  
  
public class DatabaseConnection {  
  
    private static final String URL = "jdbc:postgresql://217.142.224.197:5432/aniself";  
    private static final String USER = "****";  
    private static final String PASSWORD = "*******";  
  
    public static Connection getConnection() throws SQLException {  
        return DriverManager.getConnection(URL, USER, PASSWORD);  
    }  
}

UserDAO.java

package com.library.backend.dao;  
  
import java.sql.Connection;  
import java.sql.PreparedStatement;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
  
public class UserDAO {  
  
    // Retrieves the first user name from a table named 'users'  
    public String getFirstUserName() {  
        String sql = "SELECT name FROM users LIMIT 1;";  
        try (Connection conn = DatabaseConnection.getConnection();  
             PreparedStatement stmt = conn.prepareStatement(sql);  
             ResultSet rs = stmt.executeQuery()) {  
  
            if (rs.next()) {  
                return rs.getString("name");  
            }  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
        return null;  // Returns null if no user is found or on error  
    }  
}

Answer 1

I had a look at the PostgreSQL source code and the version of the IANA time zone database it includes, and Asia/Saigon has always been supported since the time zone library was added to the PostgreSQL source code in 2004. With update 2008c of the time zone database, which was committed to PostgreSQL on 2008-06-01, the time zone was renamed to Asia/Ho_Chi_Minh, and Asia/Saigon was left as an alias.

Now PostgreSQL offers the alternative of using it's built-in version of the time zone database or the operating system provided version. Since your PostgreSQL doesn't know Asia/Saigon, it must have been configured to use the operating system's time zone database. You can check with

pg_config --configure

The result should contain --with-system-tzdata and point to the path to your system's time zone database.

So it must be that your operating system uses a weird time zone database that doesn't know Asia/Saigon. To make PostgreSQL know Asia/Saigon, either fix (update?) the system's time zone database or build PostgreSQL from source without using the --with-system-tzdata option.

The alternative is to set the Java time zone to Asia/Ho_Chi_Minh, either by starting the JVM with

java -Duser.timezone="Asia/Ho_Chi_Minh" ...

or by setting

System.setProperty("user.timezone", "Asia/Ho_Chi_Minh");

in your Java code.