New Timestamp Data Type is Supported on UniVerse 11.3.1.6024 and Soon On 12.1.1 (Phase 1)
April 11, 2019
Timestamp data, while very common and important, is not easy to store and use at the current UniVerse versions. You must convert it to two fields in UniVerse file and use the I-Type field to combine two fields back to the original timestamp data. Now you can save timestamp data to Unix Epoch time format with mini-seconds support. In this first phase implementation, Unix Epoch time format is supported in a UniVerse file, not in an SQL table. The timestamp data is accessible via a JDBC client that is available on U2 Common Client 5.2.0 release. For the Unix Epoch time conversion, you can find more information on the https://www.epochconverter.com/ web site.
In this post, you will learn to work with new UniVerse timestamp data type and how to access this new data type via a JDBC client.
Create a NEWDATETIME2 sample file with new TIMESTAMP data type
The CREATE_NEWDATETIME2 program is used to create a NEWDATETIME2 file on UniVerse 12.1.1 or 11.3.1.6024 and includes one NEWDATETIME D-type field and one NEWDATETIME1 I-TYPE field that include new “DT” conversion code in dictionary field 3. For the U2 ODBC or JDBC client, it sets new “TIMESTAMP” data type in field 8. The timestamp data output format is “yyyy-MM-dd hh:mm:ss.SSS”.
PROGRAM CREATE_NEWDATETIME2FILENAME = ‘NEWDATETIME2’
CMD = ‘CREATE.FILE ‘:FILENAME: ‘ 2 1 1′
EXECUTE CMD
*
OPEN “DICT”, FILENAME TO FILE1 ELSE STOP “CAN NOT OPEN THE DICTIONARY FILE”
DATAX = “D”:@FM:”1″:@FM:””:@FM:”Test Data”:@FM:”30L”:@FM:”S”
WRITE DATAX TO FILE1, “TESTDATA”
DATAX = “D”:@FM:”2″:@FM:”D-YMD[4,2,2]”:@FM:”TestDate”:@FM:”10L”:@FM:”S”
WRITE DATAX TO FILE1, “TESTDATE”
DATAX = “D”:@FM:”3″:@FM:”MTZS”:@FM:”TestTime”:@FM:”10L”:@FM:”S”
WRITE DATAX TO FILE1, “TESTTIME”
DATAX = “D”:@FM:”4″:@FM:”DT”:@FM:”NewDateTime”:@FM:”20L”:@FM:”S”:@FM:””:@FM:”TIMESTAMP”
WRITE DATAX TO FILE1, “NEWDATETIME”
DATETIMEX=”OCONV(TESTDATE,’D-YMD[4,2,2]’):’ ‘:OCONV(TESTTIME,’MTS:’)”
DATAX = “I”:@FM:”ICONV(“:DATETIMEX:”,’DT’)”:@FM:”DT”:@FM:”NewDateTime1″:@FM:”20L”:@FM:”S”:@FM:””:@FM:”TIMESTAMP”
WRITE DATAX TO FILE1, “NEWDATETIME1”
DATAX = “PH”:@FM:”@ID TESTDATA TESTDATE TESTTIME NEWDATETIME NEWDATETIME1″
WRITE DATAX TO FILE1, “@”
DATAX = “PH”:@FM:”@ID TESTDATA TESTDATE TESTTIME NEWDATETIME NEWDATETIME1″
WRITE DATAX TO FILE1, “@SELECT”
DATAX = “PH”:@FM:”@ID TESTDATA TESTDATE TESTTIME NEWDATETIME”
WRITE DATAX TO FILE1, “@INSERT”
CLOSE FILE1
*- Denver time –
OPEN FILENAME TO FILE2 ELSE STOP “CAN NOT OPEN THE “:FILENAME:” FILE”
DATAX = “Test1″:@FM:”18308″:@FM:”71002″:@FM:”1518662602000”
WRITE DATAX TO FILE2, “A0001”
DATAX = “Test2″:@FM:”18489″:@FM:”71002″:@FM:”1534297402000”
WRITE DATAX TO FILE2, “B0001”
CLOSE FILE2
END
When you run the CREATE_NEWDATETIME2 program, it should generate a new NEWDATETIME2 file with new TIMESTAMP data.
LIST NEWDATETIME2 01:21:36pm 08 Mar 2019 PAGE 1
NEWDATETIME2. A0001
NEWDATETIME2. A0001
Test Data…. Test1
TestDate….. 2018-02-14
TestTime….. 19:43:22
NewDateTime.. 2018-02-14 19:43:22 (Local time)
NewDateTime1. 2018-02-14 19:43:22 (Denver time)NEWDATETIME2. B0001
NEWDATETIME2. B0001
Test Data…. Test2
TestDate….. 2018-08-14
TestTime….. 19:43:22
NewDateTime.. 2018-08-14 19:43:22 (Local time)
NewDateTime1. 2018-08-14 19:43:22 (Denver time)2 records listed.>CT DICT NEWDATETIME2 NEWDATETIME
NEWDATETIME
0001 D
0002 4
0003 DT
0004 NewDateTime
0005 20L
0006 S
0007
0008 TIMESTAMP
Note: The NewDateTime and NewDateTime1 data might be different in other time-zone locations. The sample timestamp data was set based on Denver local time. If it is running on US east coast time, you will see the two-hour time difference.
# LIST NEWDATETIME2 NEWDATETIME NEWDATETIME1
NEWDATETIME2 NewDateTime (US EST) NewDateTime1 (US Denver)
A0001 2018-02-14 21:43:22 2018-02-14 19:43:22
B0001 2018-08-14 21:43:22 2018-08-14 19:43:22
2 records listed.
Support new “DT” conversion code with ICONV and OCONV Basic functions
When using the ICONV function with new “DT” conversion code on the timestamp data, it will convert it to Unix Epoch Time format with mini-seconds. For the new OCONV function with new “DT” conversion code, it will convert the UNIX Epoch time back to readable timestamp information on local time.
Here is the example to convert the timestamp data in Denver local time. The output might be different in the other time-zone locations.
PROGRAM CONVERT_TIMESTAMPSAMPLE_TIMESTAMP_DATA = “2018-08-14 19:43:22”
CRT “Original timestamp data: “:SAMPLE_TIMESTAMP_DATA
EPOCH_TIME = ICONV(SAMPLE_TIMESTAMP_DATA,”DT”)
PRINT “Epoch time (mini-seconds): “:EPOCH_TIME
OCONV_RESULT = OCONV(EPOCH_TIME, “DT”)
PRINT “Timestamp OCONV output: “:OCONV_RESULT
END>RUN BP CONVERT_TIMESTAMP
Original timestamp data: 2018-08-14 19:43:22
Epoch time (mini-seconds): 1534297402000
Timestamp OCONV output: 2018-08-14 19:43:22
Convert the existing date and time fields to a new timestamp field
The existing date and time fields can be combined to the timestamp information as string data not timestamp data type. With the new timestamp data type support, it will be recognized on new JDBC clients during this first phase of implementation.
There is an additional I-TYPE field to combine TESTDATE and TESTTIME fields as a new TIMESTAMP field.
NEWDATETIME10001 I
0002 ICONV(OCONV(TESTDATE,’D-YMD[4,2,2]’):’ ‘:OCONV(TESTTIME,’MTS:’),’DT’)
0003 DT
0004 NewDateTime1
0005 20L
0006 S
0007
0008 TIMESTAMP
Using a JDBC client to access the UniVerse Timestamp fields
The existing date and time fields can be combined with the timestamp information as a string of data not timestamp data type. Using the new timestamp data type support, it can be recognized on a new JDBC client at the phase 1 implementation. The next JDBC example shown is running with the correct classpath environment variable setting. The JDBC client requires the asjava.zip and unijdbc.jar drivers to be defined in the classpath variable. The sample JDBC program only outputs the @ID and TIMESTAMP1 field information.
import java.sql.Connection;import java.sql.SQLException;
import java.sql.*;
import java.io.*;
public class test_timestamp {
public static void main(String[] args) {
Connection conn = null;
try {
String url = “jdbc:rs-u2://localhost/HS.SALES”;
try{ Class.forName(“com.rs.u2.jdbc.UniJDBCDriver”);
} catch (Exception ex){ex.printStackTrace();
}
conn = DriverManager.getConnection(url, “user”,”password”);
PreparedStatement stmt = null;
stmt = conn.prepareStatement(“SELECT @ID, NEWDATETIME1 FROM NEWDATETIME2”);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
System.out.println(“\tID :\t” + rs.getString(1));
System.out.println(“\tDateTime1 :\t” + rs.getTimestamp(2));
}
rs.close();
stmt.close() ;
} catch (SQLException ex) {
ex.printStackTrace();
} catch ( Exception e) {
System.out.println(“Exception caught:”+e) ;
e.printStackTrace() ;
} finally {
try { if (conn != null && !conn.isClosed()) { conn.close(); }
} catch (SQLException ex) {
ex.printStackTrace();
}}
} }
Here is the sample output using the JDBC program at Denver local time.
ID : A0001 DateTime1 : 2018-02-14 19:43:22.0 ID : B0001 DateTime1 : 2018-08-14 19:43:22.0
