Class: OCI8::BindType::DateTime

Inherits:

Object

Object
OCI8::BindType::DateTime

show all

Defined in:: lib/oci8/datetime.rb

Overview

– OCI8::BindType::DateTime ++ This is a helper class to bind ruby’s DateTime object as Oracle’s TIMESTAMP WITH TIME ZONE datatype.

Select

The fetched value for a DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE column is a DateTime. The time zone part is a session time zone if the Oracle datatype doesn’t have time zone information. The session time zone is the client machine’s time zone by default.

You can change the session time zone by executing the following SQL.

ALTER SESSION SET TIME_ZONE='-05:00'

Bind

To bind a DateTime value implicitly:

conn.exec("INSERT INTO lunar_landings(ship_name, landing_time) VALUES(:1, :2)",
          'Apollo 11',
          DateTime.parse('1969-7-20 20:17:40 00:00'))

The bind variable :2 is bound as TIMESTAMP WITH TIME ZONE on Oracle.

To bind explicitly:

cursor = conn.exec("INSERT INTO lunar_landings(ship_name, landing_time) VALUES(:1, :2)")
cursor.bind_param(':1', nil, String, 60)
cursor.bind_param(':2', nil, DateTime)
[['Apollo 11', DateTime.parse('1969-07-20 20:17:40 00:00'))],
 ['Apollo 12', DateTime.parse('1969-11-19 06:54:35 00:00'))],
 ['Apollo 14', DateTime.parse('1971-02-05 09:18:11 00:00'))],
 ['Apollo 15', DateTime.parse('1971-07-30 22:16:29 00:00'))],
 ['Apollo 16', DateTime.parse('1972-04-21 02:23:35 00:00'))],
 ['Apollo 17', DateTime.parse('1972-12-11 19:54:57 00:00'))]
].each do |ship_name, landing_time|
  cursor[':1'] = ship_name
  cursor[':2'] = landing_time
  cursor.exec
end
cursor.close

On setting a object to the bind variable, you can use any object which has at least three instance methods year, mon (or month) and mday (or day). If the object responses to hour, min, sec or sec_fraction, the responsed values are used for hour, minute, second or fraction of a second respectively. If not, zeros are set. If the object responses to offset or utc_offset, it is used for time zone. If not, the session time zone is used.

The acceptable value are listed below.

year: -4712 to 9999 [excluding year 0]
mon (or month): 0 to 12
mday (or day): 0 to 31 [depends on the month]
hour: 0 to 23
min: 0 to 59
sec: 0 to 59
sec_fraction: 0 to (999_999_999.to_r / (24*60*60* 1_000_000_000)) [999,999,999 nanoseconds]
offset: (-12.to_r / 24) to (14.to_r / 24) [-12:00 to +14:00]
utc_offset: -12*3600 <= utc_offset <= 24*3600 [-12:00 to +14:00]

The output value of the bind varible is always a DateTime.

cursor = conn.exec("BEGIN :ts := current_timestamp; END")
cursor.bind_param(:ts, nil, DateTime)
cursor.exec
cursor[:ts] # => a DateTime.
cursor.close

Class Method Summary collapse

.create(con, val, param, max_array_size) ⇒ Object

Class Method Details

.create(con, val, param, max_array_size) ⇒ `Object`

# File 'lib/oci8/datetime.rb', line 307

def self.create(con, val, param, max_array_size)
  if true # TODO: check Oracle server version
    DateTimeViaOCITimestamp.new(con, val, param, max_array_size)
  else
    DateTimeViaOCIDate.new(con, val, param, max_array_size)
  end
end