Using Timestamps, Dates, and Times

Understanding SAS Dates

When you compare date, time, and datetime values in SAS data sets, you must consider the following:

A SAS time value is the number of seconds since the current day began. That is, 0 is 00:00:00 or 12:00:00 a.m., and 86399 is 11:59:59 p.m.
The drivers return an error for time values that are less than 0 or greater than 86399 (the last second of the day).
A SAS date value is the number of days since January 1, 1960, GMT. That is, 0 is 01jan1960, and -1 is 31dec1959. The SAS date value can be read with the getDouble method.
A Java date value is the number of milliseconds since January 1, 1970, GMT. The millisecond value can be read by calling getDate().getTime().
A SAS datetime value is the number of seconds since midnight on January 1, 1960. That is, 0 is 01jan1960:00:00:00, and -1 is 31dec1959:11:59:59. The SAS datetime value can be read with the getDouble method.
A Java timestamp records the number of milliseconds since January 1, 1970, GMT and a separate nanoseconds field. The millisecond value can be read by calling getTimestamp().getTime().

Inserting Timestamps, Dates, and Times

The drivers accept the Date, Time, and Timestamp classes from the java.sql package and the JDBC escape sequences for the same types.

The following code fragment illustrates using the time-related classes in the java.sql package with a PreparedStatement:

// stmt is a Statement object created with Connection.createStatement()
stmt.executeUpdate(
  "create table sample.timeexamples (c1 date, c2 time, c3 timestamp)"
);

// Use the time-related classes in the java.sql package
// with a PreparedStatement
java.sql.Date d = new java.sql.Date(System.currentTimeMillis());
java.sql.Time t = new java.sql.Time(System.currentTimeMillis());
java.sql.Timestamp ts = new java.sql.Timestamp(System.currentTimeMillis());

PreparedStatement ps = c.prepareStatement("insert into sample.timeexamples
values (?, ?, ?)");
ps.setDate(1, d);
ps.setTime(2, t);
ps.setTimestamp(3, ts);
ps.executeUpdate();

// Let the classes in the java.sql package perform the
// JDBC escape sequences
ps.setDate(1, java.sql.Date.valueOf("2010-06-10"));
ps.setTime(2, java.sql.Time.valueOf("09:17:00"));
ps.setTimestamp(3, java.sql.Timestamp.valueOf("2010-06-10 09:17:00"));
ps.executeUpdate();

ps.close();

Alternatively, the JDBC escape syntax can be coded manually to insert timestamps (keyword ts), dates (keyword d), and times (keyword t). The following code fragment illustrates using the JDBC escape sequence with timestamp.

Timestamp ts = new Timestamp(System.currentTimeMillis());

// By using an escape clause with curly braces and the ts keyword, 
// the following code is portable across the SAS Drivers for JDBC.
String myDate = new String ("{ts '" + ts.toString() + "'}");
String query =
    "INSERT INTO work.testtable (index, ts, name) VALUES ("
        + i
        + ", "
        + myDate
        + ", "
        + n
        + ")";

Creating Timestamp, Date, and Time Columns

SAS uses double precision numbers to store all numeric data, including time-related values. In order to determine how to parse and present the values, SAS uses formats and informats. When a CREATE TABLE statement is sent to a SAS server, the time-related column definitions are parsed, and the appropriate formats are associated with the column. The following table identifies the formats that are associated with the time-related data types:

CREATE TABLE Data Types and SAS Data Types

CREATE TABLE Data Type Name	Java Data Type	SAS Format
date	java.sql.Date	DATE9.
time	java.sql.Time	TIME8.
timestamp	java.sql.Timestamp	DATETIME19.2

The data in the previous table can be helpful for readers who are familiar with SAS programming to understand how SAS interprets the following SQL statement:

create table sample.timeexamples (c1 date, c2, time, c3 timestamp);

When a SAS driver parses that statement, it converts the JDBC syntax to SAS format instructions. The previous SQL statement is passed to a SAS server as the following code:

create table sample.timeexamples (
  c1  num format=date9.,
  c2  num format=time8.,
  c3  num format=datetime19.2
);