Skip to content

Commit 119bcfa

Browse files
robfrankrobfrank
authored
#1605 Fix PostgreSQL DATETIME serialization to use ISO 8601 format (#3245)
1 parent bd2df88 commit 119bcfa

File tree

3 files changed

+145
-9
lines changed

3 files changed

+145
-9
lines changed

‎postgresw/src/main/java/com/arcadedb/postgres/PostgresNetworkExecutor.java‎

Lines changed: 21 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -414,7 +414,7 @@ private void queryCommand() {
414414
LogManager.instance().log(this, Level.INFO, "PSQL: query -> %s ", query);
415415

416416
final ResultSet resultSet;
417-
if (query.query.startsWith("SET ")) {
417+
if (query.query.toUpperCase(Locale.ENGLISH).startsWith("SET ")) {
418418
setConfiguration(query.query);
419419
resultSet = new IteratorResultSet(createResultSet("STATUS", "Setting ignored").iterator());
420420
} else if (query.query.equals("SELECT VERSION()"))
@@ -479,7 +479,7 @@ private List<Result> browseAndCacheResultSet(final ResultSet resultSet, final in
479479
private Object[] getParams(PostgresPortal portal) {
480480
Object[] parameters = portal.parameterValues != null ? portal.parameterValues.toArray() : new Object[0];
481481

482-
if (portal.language.equals("cypher")) {
482+
if (portal.language.equals("cypher") || portal.language.equals("opencypher")) {
483483
Object[] parametersCypher = new Object[parameters.length * 2];
484484
for (int i = 0; i < parameters.length; i++) {
485485
parametersCypher[i * 2] = "" + (i + 1);
@@ -1070,25 +1070,38 @@ private void parseCommand() {
10701070
}
10711071

10721072
private void setConfiguration(final String query) {
1073-
final String q = query.substring("SET ".length());
1073+
final int setLength = "SET ".length();
1074+
// Use original query to preserve case of values
1075+
final String q = query.substring(setLength);
1076+
1077+
// Try to split by either '=' or ' TO ' (case-insensitive)
10741078
String[] parts = q.split("=");
1075-
if (parts.length < 2)
1076-
parts = q.split(" TO ");
1079+
if (parts.length < 2) {
1080+
// Try case-insensitive split for " TO "
1081+
parts = q.split("(?i)\\s+TO\\s+");
1082+
}
1083+
1084+
if (parts.length < 2) {
1085+
LogManager.instance().log(this, Level.WARNING, "Invalid SET command format: %s", query);
1086+
return;
1087+
}
10771088

10781089
parts[0] = parts[0].trim();
10791090
parts[1] = parts[1].trim();
10801091

10811092
if (parts[1].startsWith("'") || parts[1].startsWith("\""))
10821093
parts[1] = parts[1].substring(1, parts[1].length() - 1);
10831094

1084-
if (parts[0].equals("datestyle")) {
1085-
if (parts[1].equals("ISO"))
1095+
// Use case-insensitive comparison for parameter names
1096+
final String paramName = parts[0].toLowerCase(Locale.ENGLISH);
1097+
if (paramName.equals("datestyle")) {
1098+
if (parts[1].equalsIgnoreCase("ISO"))
10861099
database.getSchema().setDateTimeFormat(DateUtils.DATE_TIME_ISO_8601_FORMAT);
10871100
else
10881101
LogManager.instance().log(this, Level.INFO, "datestyle '%s' not supported", parts[1]);
10891102
}
10901103

1091-
connectionProperties.put(parts[0], parts[1]);
1104+
connectionProperties.put(paramName, parts[1]);
10921105
}
10931106

10941107
private void setEmptyResultSet(final PostgresPortal portal) {

‎postgresw/src/main/java/com/arcadedb/postgres/PostgresType.java‎

Lines changed: 20 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -24,9 +24,12 @@
2424
import com.arcadedb.database.Record;
2525
import com.arcadedb.query.sql.executor.Result;
2626
import com.arcadedb.serializer.json.JSONObject;
27+
import com.arcadedb.utility.DateUtils;
2728

2829
import java.nio.ByteBuffer;
2930
import java.time.LocalDateTime;
31+
import java.time.ZoneOffset;
32+
import java.time.format.DateTimeFormatter;
3033
import java.util.ArrayList;
3134
import java.util.Arrays;
3235
import java.util.Collection;
@@ -66,6 +69,10 @@ public enum PostgresType {
6669
private static final Map<Integer, PostgresType> CODE_MAP = Arrays.stream(values())
6770
.collect(Collectors.toMap(type -> type.code, type -> type));
6871

72+
// PostgreSQL-compatible datetime format (ISO 8601 without 'T' separator)
73+
private static final String POSTGRES_TIMESTAMP_FORMAT = "yyyy-MM-dd HH:mm:ss.SSSSSS";
74+
private static final DateTimeFormatter POSTGRES_DATETIME_FORMATTER = DateTimeFormatter.ofPattern(POSTGRES_TIMESTAMP_FORMAT);
75+
6976
public final int code;
7077
public final Class<?> cls;
7178
public final int size;
@@ -251,6 +258,13 @@ public void serializeAsText(final PostgresType pgType, final Binary typeBuffer,
251258
// Handle primitive arrays by converting them to Collections
252259
Collection<?> collection = convertPrimitiveArrayToCollection(value);
253260
serializedValue = serializeArrayToString(collection, pgType);
261+
} else if (value instanceof Date date) {
262+
// Format Date as PostgreSQL-compatible timestamp
263+
LocalDateTime ldt = LocalDateTime.ofInstant(date.toInstant(), ZoneOffset.UTC);
264+
serializedValue = ldt.format(POSTGRES_DATETIME_FORMATTER);
265+
} else if (value instanceof LocalDateTime ldt) {
266+
// Format LocalDateTime as PostgreSQL-compatible timestamp
267+
serializedValue = ldt.format(POSTGRES_DATETIME_FORMATTER);
254268
} else if (value instanceof JSONObject json) {
255269
serializedValue = json.toString();
256270
} else if (value instanceof Map map) {
@@ -301,7 +315,12 @@ private String serializeArrayToString(Collection<?> collection, PostgresType pgT
301315
} else if (element instanceof Character) {
302316
sb.append("'").append(element).append("'");
303317
} else if (element instanceof Date date) {
304-
sb.append(date.getTime());
318+
// Format Date as PostgreSQL-compatible timestamp in arrays
319+
LocalDateTime ldt = LocalDateTime.ofInstant(date.toInstant(), ZoneOffset.UTC);
320+
sb.append("\"").append(ldt.format(POSTGRES_DATETIME_FORMATTER)).append("\"");
321+
} else if (element instanceof LocalDateTime ldt) {
322+
// Format LocalDateTime as PostgreSQL-compatible timestamp in arrays
323+
sb.append("\"").append(ldt.format(POSTGRES_DATETIME_FORMATTER)).append("\"");
305324
} else if (element instanceof Binary binary) {
306325
sb.append(binary.getString());
307326
} else if (element instanceof byte[] bytes) {

‎postgresw/src/test/java/com/arcadedb/postgres/PostgresWJdbcIT.java‎

Lines changed: 104 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -372,6 +372,110 @@ void isoDateFormat() throws Exception {
372372
}
373373
}
374374

375+
/**
376+
* Test for issue #1605: PostgreSQL DATETIME serialization should produce ISO 8601 format
377+
* that is compatible with PostgreSQL clients (especially node-postgres library).
378+
*/
379+
@Test
380+
void dateTimeSerializationFormat() throws Exception {
381+
try (final Connection conn = getConnection()) {
382+
conn.setAutoCommit(false);
383+
try (var st = conn.createStatement()) {
384+
st.execute("CREATE VERTEX TYPE TestDateTime IF NOT EXISTS");
385+
st.execute("CREATE PROPERTY TestDateTime.created IF NOT EXISTS DATETIME");
386+
387+
// Insert a specific datetime value
388+
st.execute("CREATE VERTEX TestDateTime SET name = 'test1', created = '2024-05-19 17:05:11'");
389+
390+
// Query the datetime value
391+
ResultSet rs = st.executeQuery("SELECT created FROM TestDateTime WHERE name = 'test1'");
392+
393+
assertThat(rs.next()).isTrue();
394+
395+
// Verify the timestamp is not null (would be null if pg driver can't parse the format)
396+
java.sql.Timestamp timestamp = rs.getTimestamp("created");
397+
assertThat(timestamp).isNotNull();
398+
399+
// Verify the value is correct
400+
assertThat(timestamp.toString()).startsWith("2024-05-19 17:05:11");
401+
402+
rs.close();
403+
}
404+
}
405+
}
406+
407+
/**
408+
* Test for issue #1605: SET datestyle command should be case-insensitive
409+
*/
410+
@Test
411+
void setDateStyleCaseInsensitive() throws Exception {
412+
try (final Connection conn = getConnection()) {
413+
try (var st = conn.createStatement()) {
414+
// All these variations should work without errors
415+
st.execute("set datestyle to 'ISO'");
416+
st.execute("SET DATESTYLE TO 'ISO'");
417+
st.execute("Set DateStyle To 'ISO'");
418+
st.execute("SET datestyle = 'ISO'");
419+
}
420+
}
421+
}
422+
423+
/**
424+
* Test for issue #1605: SET command should preserve case of values
425+
*/
426+
@Test
427+
void setCommandPreservesCaseOfValues() throws Exception {
428+
try (final Connection conn = getConnection()) {
429+
try (var st = conn.createStatement()) {
430+
// Execute SET with mixed case value
431+
st.execute("SET application_name = 'MyApp'");
432+
st.execute("SET client_encoding = 'UTF8'");
433+
434+
// The SET command should preserve the original case of values
435+
// This test verifies the fix doesn't uppercase values like 'MyApp' to 'MYAPP'
436+
// Note: We can't directly verify the stored value through JDBC,
437+
// but the test ensures no exceptions are thrown and the command is accepted
438+
}
439+
}
440+
}
441+
442+
/**
443+
* Test for issue #1605: Datetime arrays should be serialized correctly
444+
*/
445+
@Test
446+
void dateTimeArraySerialization() throws Exception {
447+
try (final Connection conn = getConnection()) {
448+
conn.setAutoCommit(false);
449+
try (var st = conn.createStatement()) {
450+
st.execute("CREATE VERTEX TYPE TestDateTimeArray IF NOT EXISTS");
451+
st.execute("CREATE PROPERTY TestDateTimeArray.dates IF NOT EXISTS LIST");
452+
453+
// Insert an array of datetime values
454+
st.execute("CREATE VERTEX TestDateTimeArray SET name = 'test1', dates = ['2024-05-19 17:05:11', '2024-05-20 18:06:12']");
455+
456+
// Query the datetime array
457+
ResultSet rs = st.executeQuery("SELECT dates FROM TestDateTimeArray WHERE name = 'test1'");
458+
459+
assertThat(rs.next()).isTrue();
460+
461+
// Verify the array is not null
462+
Array datesArray = rs.getArray("dates");
463+
assertThat(datesArray).isNotNull();
464+
465+
// Get array elements
466+
Object[] dates = (Object[]) datesArray.getArray();
467+
assertThat(dates).isNotNull();
468+
assertThat(dates).hasSize(2);
469+
470+
// Verify dates are strings in correct format (PostgreSQL JDBC driver will parse them)
471+
assertThat(dates[0]).isNotNull();
472+
assertThat(dates[1]).isNotNull();
473+
474+
rs.close();
475+
}
476+
}
477+
}
478+
375479
@Test
376480
@Disabled
377481
void waitForConnectionFromExternal() throws Exception {

0 commit comments

Comments
 (0)