in src/main/java/com/microsoft/sqlserver/jdbc/dtv.java [429:988]
private void sendTemporal(DTV dtv, JavaType javaType, Object value) throws SQLServerException {
JDBCType jdbcType = dtv.getJdbcType();
GregorianCalendar calendar = null;
int subSecondNanos = 0;
int minutesOffset = 0;
/*
* Some precisions to consider: java.sql.Time is millisecond precision java.sql.Timestamp is nanosecond
* precision java.util.Date is millisecond precision java.util.Calendar is millisecond precision
* java.time.LocalTime is nanosecond precision java.time.LocalDateTime is nanosecond precision
* java.time.OffsetTime is nanosecond precision, with a zone offset java.time.OffsetDateTime is nanosecond
* precision, with a zone offset SQL Server Types: datetime2 is 7 digit precision, i.e 100 ns (default and
* max) datetime is 3 digit precision, i.e ms precision (default and max) time is 7 digit precision, i.e 100
* ns (default and max) Note: sendTimeAsDatetime is true by default and it actually sends the time value as
* datetime, not datetime2 which looses precision values as datetime is only MS precision (1/300 of a second
* to be precise). Null values pass right on through to the typed writers below. For non-null values, load
* the value from its original Java object (java.sql.Time, java.sql.Date, java.sql.Timestamp,
* java.util.Date, java.time.LocalDate, java.time.LocalTime, java.time.LocalDateTime or
* microsoft.sql.DateTimeOffset) into a Gregorian calendar. Don't use the DTV's calendar directly, as it may
* not be Gregorian...
*/
if (null != value) {
TimeZone timeZone = TimeZone.getDefault(); // Time zone to associate with the value in the Gregorian
// calendar
long utcMillis = 0; // Value to which the calendar is to be set (in milliseconds 1/1/1970 00:00:00 GMT)
// Figure out the value components according to the type of the Java object passed in...
switch (javaType) {
case TIME: {
// Set the time zone from the calendar supplied by the app or use the JVM default
timeZone = (null != dtv.getCalendar()) ? dtv.getCalendar().getTimeZone()
: TimeZone.getDefault();
utcMillis = ((java.sql.Time) value).getTime();
subSecondNanos = Nanos.PER_MILLISECOND * (int) (utcMillis % 1000);
// The utcMillis value may be negative for morning times in time zones east of GMT.
// Since the date part of the java.sql.Time object is normalized to 1/1/1970
// in the local time zone, the date may still be 12/31/1969 UTC.
//
// If that is the case then adjust the sub-second nanos to the correct non-negative
// "wall clock" value. For example: -1 nanos (one nanosecond before midnight) becomes
// 999999999 nanos (999,999,999 nanoseconds after 11:59:59).
if (subSecondNanos < 0)
subSecondNanos += Nanos.PER_SECOND;
break;
}
case DATE: {
// Set the time zone from the calendar supplied by the app or use the JVM default
timeZone = (null != dtv.getCalendar()) ? dtv.getCalendar().getTimeZone()
: TimeZone.getDefault();
utcMillis = ((java.sql.Date) value).getTime();
break;
}
case TIMESTAMP: {
// Set the time zone from the calendar supplied by the app or use the JVM default
timeZone = (null != dtv.getCalendar()) ? dtv.getCalendar().getTimeZone()
: TimeZone.getDefault();
java.sql.Timestamp timestampValue = (java.sql.Timestamp) value;
utcMillis = timestampValue.getTime();
subSecondNanos = timestampValue.getNanos();
break;
}
case UTILDATE: {
// java.util.Date is mapped to JDBC type TIMESTAMP
// java.util.Date and java.sql.Date are both millisecond precision
// Set the time zone from the calendar supplied by the app or use the JVM default
timeZone = (null != dtv.getCalendar()) ? dtv.getCalendar().getTimeZone()
: TimeZone.getDefault();
utcMillis = ((java.util.Date) value).getTime();
// Need to use the subsecondnanoes part in UTILDATE besause it is mapped to JDBC TIMESTAMP. This
// is not
// needed in DATE because DATE is mapped to JDBC DATE (with time part normalized to midnight)
subSecondNanos = Nanos.PER_MILLISECOND * (int) (utcMillis % 1000);
// The utcMillis value may be negative for morning times in time zones east of GMT.
// Since the date part of the java.sql.Time object is normalized to 1/1/1970
// in the local time zone, the date may still be 12/31/1969 UTC.
//
// If that is the case then adjust the sub-second nanos to the correct non-negative
// "wall clock" value. For example: -1 nanos (one nanosecond before midnight) becomes
// 999999999 nanos (999,999,999 nanoseconds after 11:59:59).
if (subSecondNanos < 0)
subSecondNanos += Nanos.PER_SECOND;
break;
}
case CALENDAR: {
// java.util.Calendar is mapped to JDBC type TIMESTAMP
// java.util.Calendar is millisecond precision
// Set the time zone from the calendar supplied by the app or use the JVM default
timeZone = (null != dtv.getCalendar()) ? dtv.getCalendar().getTimeZone()
: TimeZone.getDefault();
utcMillis = ((java.util.Calendar) value).getTimeInMillis();
// Need to use the subsecondnanoes part in CALENDAR besause it is mapped to JDBC TIMESTAMP. This
// is not
// needed in DATE because DATE is mapped to JDBC DATE (with time part normalized to midnight)
subSecondNanos = Nanos.PER_MILLISECOND * (int) (utcMillis % 1000);
// The utcMillis value may be negative for morning times in time zones east of GMT.
// Since the date part of the java.sql.Time object is normalized to 1/1/1970
// in the local time zone, the date may still be 12/31/1969 UTC.
//
// If that is the case then adjust the sub-second nanos to the correct non-negative
// "wall clock" value. For example: -1 nanos (one nanosecond before midnight) becomes
// 999999999 nanos (999,999,999 nanoseconds after 11:59:59).
if (subSecondNanos < 0)
subSecondNanos += Nanos.PER_SECOND;
break;
}
case LOCALDATE:
// Mapped to JDBC type DATE
calendar = new GregorianCalendar(UTC.timeZone, Locale.US);
// All time fields are set to default
clearSetCalendar(calendar, true, ((LocalDate) value).getYear(),
((LocalDate) value).getMonthValue() - 1, // Calendar 'month'
// is 0-based, but
// LocalDate 'month'
// is 1-based
((LocalDate) value).getDayOfMonth(), null, null, null);
break;
case LOCALTIME:
// Nanoseconds precision, mapped to JDBC type TIME
calendar = new GregorianCalendar(UTC.timeZone, Locale.US);
// All date fields are set to default
LocalTime LocalTimeValue = ((LocalTime) value);
clearSetCalendar(calendar, true, conn.baseYear(), 1, 1, LocalTimeValue.getHour(), // Gets
// hour_of_day
// field
LocalTimeValue.getMinute(), LocalTimeValue.getSecond());
subSecondNanos = LocalTimeValue.getNano();
// Do not need to adjust subSecondNanos as in the case for TIME
// because LOCALTIME does not have time zone and is not using utcMillis
break;
case LOCALDATETIME:
// Nanoseconds precision, mapped to JDBC type TIMESTAMP
calendar = new GregorianCalendar(UTC.timeZone, Locale.US);
// Calendar 'month' is 0-based, but LocalDateTime 'month' is 1-based
LocalDateTime localDateTimeValue = (LocalDateTime) value;
clearSetCalendar(calendar, true, localDateTimeValue.getYear(),
localDateTimeValue.getMonthValue() - 1, localDateTimeValue.getDayOfMonth(),
localDateTimeValue.getHour(), // Gets hour_of_day field
localDateTimeValue.getMinute(), localDateTimeValue.getSecond());
subSecondNanos = localDateTimeValue.getNano();
// Do not need to adjust subSecondNanos as in the case for TIME
// because LOCALDATETIME does not have time zone and is not using utcMillis
break;
case OFFSETTIME:
OffsetTime offsetTimeValue = (OffsetTime) value;
try {
// offsetTimeValue.getOffset() returns a ZoneOffset object which has only hours and minutes
// components. So the result of the division will be an integer always. SQL Server also
// supports
// offsets in minutes precision.
minutesOffset = offsetTimeValue.getOffset().getTotalSeconds() / 60;
} catch (Exception e) {
throw new SQLServerException(SQLServerException.getErrString("R_zoneOffsetError"), null, // SQLState
// is
// null
// as
// this
// error
// is
// generated
// in
// the
// driver
0, // Use 0 instead of DriverError.NOT_SET to use the correct constructor
e);
}
subSecondNanos = offsetTimeValue.getNano();
// If the target data type is TIME_WITH_TIMEZONE, then use UTC for the calendar that
// will hold the value, since writeRPCDateTimeOffset expects a UTC calendar.
// Otherwise, when converting from DATETIMEOFFSET to other temporal data types,
// use a local time zone determined by the minutes offset of the value, since
// the writers for those types expect local calendars.
timeZone = (JDBCType.TIME_WITH_TIMEZONE == jdbcType
&& (null == typeInfo || SSType.DATETIMEOFFSET == typeInfo.getSSType())) ?
UTC.timeZone
: new SimpleTimeZone(
minutesOffset
* 60
* 1000,
"");
LocalDate baseDate = LocalDate.of(conn.baseYear(), 1, 1);
utcMillis = offsetTimeValue.atDate(baseDate).toEpochSecond() * 1000;
break;
case OFFSETDATETIME:
OffsetDateTime offsetDateTimeValue = (OffsetDateTime) value;
try {
// offsetTimeValue.getOffset() returns a ZoneOffset object which has only hours and minutes
// components. So the result of the division will be an integer always. SQL Server also
// supports
// offsets in minutes precision.
minutesOffset = offsetDateTimeValue.getOffset().getTotalSeconds() / 60;
} catch (Exception e) {
throw new SQLServerException(SQLServerException.getErrString("R_zoneOffsetError"), null, // SQLState
// is
// null
// as
// this
// error
// is
// generated
// in
// the
// driver
0, // Use 0 instead of DriverError.NOT_SET to use the correct constructor
e);
}
subSecondNanos = offsetDateTimeValue.getNano();
// If the target data type is TIME_WITH_TIMEZONE or TIMESTAMP_WITH_TIMEZONE, then use UTC for
// the calendar that
// will hold the value, since writeRPCDateTimeOffset expects a UTC calendar.
// Otherwise, when converting from DATETIMEOFFSET to other temporal data types,
// use a local time zone determined by the minutes offset of the value, since
// the writers for those types expect local calendars.
timeZone = ((JDBCType.TIMESTAMP_WITH_TIMEZONE == jdbcType
|| JDBCType.TIME_WITH_TIMEZONE == jdbcType)
&& (null == typeInfo || SSType.DATETIMEOFFSET == typeInfo.getSSType()))
? UTC.timeZone
: new SimpleTimeZone(
minutesOffset
* 60
* 1000,
"");
utcMillis = offsetDateTimeValue.toEpochSecond() * 1000;
break;
case DATETIMEOFFSET: {
microsoft.sql.DateTimeOffset dtoValue = (microsoft.sql.DateTimeOffset) value;
utcMillis = dtoValue.getTimestamp().getTime();
subSecondNanos = dtoValue.getTimestamp().getNanos();
minutesOffset = dtoValue.getMinutesOffset();
// microsoft.sql.DateTimeOffset values have a time zone offset that is internal
// to the value, so there should not be any DTV calendar for DateTimeOffset values.
assert null == dtv.getCalendar();
// If the target data type is DATETIMEOFFSET, then use UTC for the calendar that
// will hold the value, since writeRPCDateTimeOffset expects a UTC calendar.
// Otherwise, when converting from DATETIMEOFFSET to other temporal data types,
// use a local time zone determined by the minutes offset of the value, since
// the writers for those types expect local calendars.
timeZone = (JDBCType.DATETIMEOFFSET == jdbcType
&& (null == typeInfo || SSType.DATETIMEOFFSET == typeInfo.getSSType()
|| SSType.VARBINARY == typeInfo.getSSType()
|| SSType.VARBINARYMAX == typeInfo.getSSType())) ?
UTC.timeZone
: new SimpleTimeZone(
minutesOffset * 60
* 1000,
"");
break;
}
default:
throw new AssertionError("Unexpected JavaType: " + javaType);
}
// For the LocalDate, LocalTime and LocalDateTime values, calendar should be set by now.
if (null == calendar) {
// Create the calendar that will hold the value. For DateTimeOffset values, the calendar's
// time zone is UTC. For other values, the calendar's time zone is a local time zone.
calendar = new GregorianCalendar(timeZone, Locale.US);
// Set the calendar lenient to allow setting the DAY_OF_YEAR and MILLISECOND fields
// to roll other fields to their correct values.
calendar.setLenient(true);
// Clear the calendar of any existing state. The state of a new Calendar object always
// reflects the current date, time, DST offset, etc.
calendar.clear();
// Load the calendar with the desired value
calendar.setTimeInMillis(utcMillis);
}
}
// With the value now stored in a Calendar object, determine the backend data type and
// write out the value to the server, after any necessarily normalization.
// typeInfo is null when called from PreparedStatement->Parameter->SendByRPC
if (null != typeInfo) // updater
{
switch (typeInfo.getSSType()) {
case DATETIME:
case DATETIME2:
/*
* Default and max fractional precision is 7 digits (100ns) Send DateTime2 to DateTime columns
* to let the server handle nanosecond rounding. Also adjust scale accordingly to avoid rounding
* on driver's end.
*/
int scale = (typeInfo.getSSType() == SSType.DATETIME) ? typeInfo.getScale() + 4
: typeInfo.getScale();
tdsWriter.writeRPCDateTime2(name,
timestampNormalizedCalendar(calendar, javaType, conn.baseYear()), subSecondNanos, scale,
isOutParam);
break;
case DATE:
tdsWriter.writeRPCDate(name, calendar, isOutParam);
break;
case TIME:
// Default and max fractional precision is 7 digits (100ns)
tdsWriter.writeRPCTime(name, calendar, subSecondNanos, typeInfo.getScale(), isOutParam);
break;
case DATETIMEOFFSET:
// When converting from any other temporal Java type to DATETIMEOFFSET,
// deliberately interpret the "wall calendar" representation as expressing
// a date/time in UTC rather than the local time zone.
if (JavaType.DATETIMEOFFSET != javaType) {
calendar = timestampNormalizedCalendar(localCalendarAsUTC(calendar), javaType,
conn.baseYear());
minutesOffset = 0; // UTC
}
tdsWriter.writeRPCDateTimeOffset(name, calendar, minutesOffset, subSecondNanos,
typeInfo.getScale(), isOutParam);
break;
case SMALLDATETIME:
tdsWriter.writeRPCDateTime(name,
timestampNormalizedCalendar(calendar, javaType, conn.baseYear()), subSecondNanos,
isOutParam);
break;
case VARBINARY:
case VARBINARYMAX:
switch (jdbcType) {
case DATETIME:
case SMALLDATETIME:
tdsWriter.writeEncryptedRPCDateTime(name,
timestampNormalizedCalendar(calendar, javaType, conn.baseYear()),
subSecondNanos, isOutParam, jdbcType, statement);
break;
case TIMESTAMP:
assert null != cryptoMeta;
tdsWriter.writeEncryptedRPCDateTime2(name,
timestampNormalizedCalendar(calendar, javaType, conn.baseYear()),
subSecondNanos, valueLength, isOutParam, statement);
break;
case TIME:
// when colum is encrypted, always send time as time, ignore sendTimeAsDatetime setting
assert null != cryptoMeta;
tdsWriter.writeEncryptedRPCTime(name, calendar, subSecondNanos, valueLength,
isOutParam, statement);
break;
case DATE:
assert null != cryptoMeta;
tdsWriter.writeEncryptedRPCDate(name, calendar, isOutParam, statement);
break;
case TIMESTAMP_WITH_TIMEZONE:
case DATETIMEOFFSET:
// When converting from any other temporal Java type to
// DATETIMEOFFSET/TIMESTAMP_WITH_TIMEZONE,
// deliberately reinterpret the value as local to UTC. This is to match
// SQL Server behavior for such conversions.
if ((JavaType.DATETIMEOFFSET != javaType) && (JavaType.OFFSETDATETIME != javaType)) {
calendar = timestampNormalizedCalendar(localCalendarAsUTC(calendar), javaType,
conn.baseYear());
minutesOffset = 0; // UTC
}
assert null != cryptoMeta;
tdsWriter.writeEncryptedRPCDateTimeOffset(name, calendar, minutesOffset, subSecondNanos,
valueLength, isOutParam, statement);
break;
default:
assert false : "Unexpected JDBCType: " + jdbcType;
}
break;
default:
assert false : "Unexpected SSType: " + typeInfo.getSSType();
}
} else // setter
{
// Katmai and later
// ----------------
//
// When sending as...
// - java.sql.Types.TIMESTAMP, use DATETIME2 SQL Server data type
// - java.sql.Types.TIME, use TIME or DATETIME SQL Server data type
// as determined by sendTimeAsDatetime setting
// - java.sql.Types.DATE, use DATE SQL Server data type
// - microsoft.sql.Types.DATETIMEOFFSET, use DATETIMEOFFSET SQL Server data type
if (conn.isKatmaiOrLater()) {
if (aeLogger.isLoggable(java.util.logging.Level.FINE) && (null != cryptoMeta)) {
aeLogger.fine("Encrypting temporal data type.");
}
switch (jdbcType) {
case DATETIME:
case SMALLDATETIME:
case TIMESTAMP:
if (null != cryptoMeta) {
if ((JDBCType.DATETIME == jdbcType) || (JDBCType.SMALLDATETIME == jdbcType)) {
tdsWriter.writeEncryptedRPCDateTime(name,
timestampNormalizedCalendar(calendar, javaType, conn.baseYear()),
subSecondNanos, isOutParam, jdbcType, statement);
} else if (0 == valueLength) {
tdsWriter.writeEncryptedRPCDateTime2(name,
timestampNormalizedCalendar(calendar, javaType, conn.baseYear()),
subSecondNanos, outScale, isOutParam, statement);
} else {
tdsWriter.writeEncryptedRPCDateTime2(name,
timestampNormalizedCalendar(calendar, javaType, conn.baseYear()),
subSecondNanos, (valueLength), isOutParam, statement);
}
} else
tdsWriter.writeRPCDateTime2(name,
timestampNormalizedCalendar(calendar, javaType, conn.baseYear()),
subSecondNanos, TDS.MAX_FRACTIONAL_SECONDS_SCALE, isOutParam);
break;
case TIME:
// if column is encrypted, always send as TIME
if (null != cryptoMeta) {
if (0 == valueLength) {
tdsWriter.writeEncryptedRPCTime(name, calendar, subSecondNanos, outScale,
isOutParam, statement);
} else {
tdsWriter.writeEncryptedRPCTime(name, calendar, subSecondNanos, valueLength,
isOutParam, statement);
}
} else {
// Send the java.sql.Types.TIME value as TIME or DATETIME SQL Server
// data type, based on sendTimeAsDatetime setting.
if (conn.getSendTimeAsDatetime()) {
tdsWriter.writeRPCDateTime(name,
timestampNormalizedCalendar(calendar, JavaType.TIME, TDS.BASE_YEAR_1970),
subSecondNanos, isOutParam);
} else {
tdsWriter.writeRPCTime(name, calendar, subSecondNanos,
TDS.MAX_FRACTIONAL_SECONDS_SCALE, isOutParam);
}
}
break;
case DATE:
if (null != cryptoMeta)
tdsWriter.writeEncryptedRPCDate(name, calendar, isOutParam, statement);
else
tdsWriter.writeRPCDate(name, calendar, isOutParam);
break;
case TIME_WITH_TIMEZONE:
// When converting from any other temporal Java type to TIME_WITH_TIMEZONE,
// deliberately reinterpret the value as local to UTC. This is to match
// SQL Server behavior for such conversions.
if ((JavaType.OFFSETDATETIME != javaType) && (JavaType.OFFSETTIME != javaType)) {
calendar = timestampNormalizedCalendar(localCalendarAsUTC(calendar), javaType,
conn.baseYear());
minutesOffset = 0; // UTC
}
tdsWriter.writeRPCDateTimeOffset(name, calendar, minutesOffset, subSecondNanos,
TDS.MAX_FRACTIONAL_SECONDS_SCALE, isOutParam);
break;
case TIMESTAMP_WITH_TIMEZONE:
case DATETIMEOFFSET:
// When converting from any other temporal Java type to
// DATETIMEOFFSET/TIMESTAMP_WITH_TIMEZONE,
// deliberately reinterpret the value as local to UTC. This is to match
// SQL Server behavior for such conversions.
if ((JavaType.DATETIMEOFFSET != javaType) && (JavaType.OFFSETDATETIME != javaType)) {
calendar = timestampNormalizedCalendar(localCalendarAsUTC(calendar), javaType,
conn.baseYear());
minutesOffset = 0; // UTC
}
if (null != cryptoMeta) {
if (0 == valueLength) {
tdsWriter.writeEncryptedRPCDateTimeOffset(name, calendar, minutesOffset,
subSecondNanos, outScale, isOutParam, statement);
} else {
tdsWriter.writeEncryptedRPCDateTimeOffset(name, calendar, minutesOffset,
subSecondNanos,
(0 == valueLength ? TDS.MAX_FRACTIONAL_SECONDS_SCALE : valueLength),
isOutParam, statement);
}
} else
tdsWriter.writeRPCDateTimeOffset(name, calendar, minutesOffset, subSecondNanos,
TDS.MAX_FRACTIONAL_SECONDS_SCALE, isOutParam);
break;
default:
assert false : "Unexpected JDBCType: " + jdbcType;
}
}
// Yukon and earlier
// -----------------
//
// When sending as...
// - java.sql.Types.TIMESTAMP, use DATETIME SQL Server data type (all components)
// - java.sql.Types.TIME, use DATETIME SQL Server data type (with date = 1/1/1970)
// - java.sql.Types.DATE, use DATETIME SQL Server data type (with time = midnight)
// - microsoft.sql.Types.DATETIMEOFFSET (not supported - exception should have been thrown earlier)
else {
assert JDBCType.TIME == jdbcType || JDBCType.DATE == jdbcType
|| JDBCType.TIMESTAMP == jdbcType : "Unexpected JDBCType: " + jdbcType;
tdsWriter.writeRPCDateTime(name,
timestampNormalizedCalendar(calendar, javaType, TDS.BASE_YEAR_1970), subSecondNanos,
isOutParam);
}
} // setters
}