New TIMESTAMP data type is supported on UniVerse 11.3.1.6024 and soon on 12.1.1 (Phase 1)

Overview

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_NEWDATETIME2

FILENAME = ‘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_TIMESTAMP

SAMPLE_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.

     NEWDATETIME1

0001 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

Avatar

Paul Chang 7 Posts

Paul Chang is a Principal Technical Support Engineer at Rocket Software. He is responsible for supporting all U2 common middleware client products, Java products and .NET clients. He has 35 years of experience in computing and holds a master’s degree in Computer Science with an emphasis on Relational Database Design.

2 Comments

  • Avatar

    Ian McGowan Reply

    April 14, 2019 at 10:25 am

    Paul, really nice feature, thanks for sharing. Code examples are great, but the blog software does a terrible job of displaying code 🙂 This is a nice feature for people with applications supporting users in multiple time-zones.

    Is TIMESTAMP coming to Unidata any time soon? It seems like one of those rare features that doesn’t rely on database internals as much as some new features.

    Is the time stored in UTC and converted to local time when using ICONV/OCONV?

    What is “epoch time (mini-seconds)”? Epoch time is to the second, and Epoch time in milliseconds is the number of milliseconds since the epoch. Is mini-seconds a tenth or hundredth of a second, instead of the more usual thousandth?

    Thanks again!

    • Kathy Larson

      Kathy Larson Reply

      April 15, 2019 at 8:15 am

      The new TIMESTAMP feature for UniData should be available on next major release (9.0). For the Epoch time system design, it can be seconds, mini-seconds (0.001) or micro-seconds (0.000001). We decide to use the mini-seconds as internal format. All timestamp data will be stored in UTC when using ICONV/OCONV function. For all U2 functions, th TIMESTAMP information will be converted based on locale time-zone automatically. For example, you have the timestamp data created in US Mountain Time-Zone area, and move the database to east-cost area server. The output of timestamp will see the two-hours difference. If the database server is not relocated to any other area, the input/output timestamp information should be same as always.

Leave a Comment

Your email address will not be published. Required fields are marked *