4.2.7 Date, Time, and Integer Data
OML4Py provides the data types that enable you to manipulate date, time and integer.
oml.Datetimeoml.Timezoneoml.Timedeltaoml.Integer
oml.Datetime,
oml.Timezone, oml.Timedelta and
oml.Integer, see Oracle Machine Learning for Python API
Reference.
oml.Datetime
To create a date, you can use the datetime class of the datetime module, which is included in OML4Py.
The datetime class requires three parameters: year, month, and day. It also contains optional parameters for time and timezone that includes hour, minute, second, microsecond, and tzone.
Example 4-13 Using the oml.Datetime Function
This example creates a proxy object from a table with DATE column.
import oml
import pandas as pd
import numpy as np
import datetime
from datetime import datetime, timezone, timedelta
SALES = oml.sync(schema="SH", table="SALES")
z.show(SALES.head())
# Use the following command to compute the statistics on table columns:
pd.set_option('display.max_columns', 50)
pd.set_option('display.width', 1000)
SALES.describe(include='all')
# Use the following command to compute statistics on DATE column TIME_ID:
SALES['TIME_ID'].describe()
# Use the following command to extract date-related features:
date = SALES['TIME_ID']
SALES2 = SALES.concat({'YEAR': date.year, 'MONTH': date.month})
SALES2.head()Listing for This Example
>>> import oml
>>> import pandas as pd
>>> import numpy as np
>>> import datetime
>>> from datetime import datetime, timezone, timedelta
>>> SALES = oml.sync(schema="SH", table="SALES")
>>> z.show(SALES.head())
>>> PROD_ID CUST_ID TIME_ID CHANNEL_ID PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
13 524 1998-01-20 00:00:00 2 999 1.0 1205.99
13 2128 1998-04-05 00:00:00 2 999 1.0 1250.25
13 3212 1998-04-05 00:00:00 2 999 1.0 1250.25
13 3375 1998-04-05 00:00:00 2 999 1.0 1250.25
13 5204 1998-04-05 00:00:00 2 999 1.0 1250.25
>>> pd.set_option('display.max_columns', 50)
>>> pd.set_option('display.width', 1000)
>>> SALES.describe(include='all')
>>> PROD_ID CUST_ID TIME_ID CHANNEL_ID PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
count 918843.000000 918843.000000 918843 918843.000000 918843.000000 918843.0 918843.000000
unique NaN NaN 1460 NaN NaN NaN NaN
top NaN NaN 2001-10-18 00:00:00 NaN NaN NaN NaN
freq NaN NaN 2940 NaN NaN NaN NaN
mean 78.183945 7289.807720 NaN 2.861603 976.396093 1.0 106.879882
std 49.008014 8948.653221 NaN 0.686874 121.829887 0.0 259.780490
min 13.000000 2.000000 NaN 2.000000 33.000000 1.0 6.400000
25% 31.000000 2383.000000 NaN 2.000000 999.000000 1.0 17.380000
50% 48.000000 4927.000000 NaN 3.000000 999.000000 1.0 34.240000
75% 127.000000 9163.000000 NaN 3.000000 999.000000 1.0 53.890000
max 148.000000 101000.000000 NaN 9.000000 999.000000 1.0 1782.720000
>>> SALES['TIME_ID'].describe()
>>> count 918843
unique 1460
top 2001-10-18 00:00:00
freq 2940
Name: TIME_ID, dtype: object
>>> date = SALES['TIME_ID']
>>> SALES2 = SALES.concat({'YEAR': date.year, 'MONTH': date.month})
>>> SALES2.head()
>>> PROD_ID CUST_ID TIME_ID CHANNEL_ID PROMO_ID QUANTITY_SOLD AMOUNT_SOLD YEAR MONTH
0 13 524 1998-01-20 2 999 1.0 1205.99 1998 1
1 13 2128 1998-04-05 2 999 1.0 1250.25 1998 4
2 13 3212 1998-04-05 2 999 1.0 1250.25 1998 4
3 13 3375 1998-04-05 2 999 1.0 1250.25 1998 4
4 13 5204 1998-04-05 2 999 1.0 1250.25 1998 4Example 4-14 Using the oml.Datetime Function
This example creates a datetime object with the year, month, day, then creates a temporary proxy object using oml.push. The oml.push function requires a data frame or list as input, so the date object is converted to a list. The resulting object is an object of class oml.Datetime.
import oml
import pandas as pd
import numpy as np
import datetime
from datetime import datetime, timezone, timedelta
d1 = datetime(year=2004, month=7, day=24)
print ('d1:', d1)
print('d1 year:', d1.year)
print('d1 month:', d1.month)
d1_lst = [d1]
D1 = oml.push(d1_lst)
print ('type', type(D1))
print ('D1:', D1)
print ('year:', D1.year)
print ('month:', D1.month)
print ('day:', D1.day)
d2 = datetime.fromisoformat('2004-07-24 00:05:23+04:00')
d2_lst=[d2]
D2 = oml.push(d2_lst)
print('D2', D2)
print('type', type(D2))
D2.strftime()
D2.strftime()
d3 = "14-Jul-05 20:01:01"
d3_lst = [d3]
D3 = oml.push(d3_lst)
oml.Datetime.strptime(D3, "DD-Mon-RR HH24:MI:SS")Listing for This Example
>>> import oml
>>> import pandas as pd
>>> import numpy as np
>>> import datetime
>>> from datetime import datetime, timezone, timedelta
>>> d1 = datetime(year=2004, month=7, day=24)
>>> print ('d1:', d1)
>>> d1: 2004-07-24 00:00:00
>>> print('d1 year:', d1.year)
>>> d1 year: 2004
>>> print('d1 month:', d1.month)
>>> d1 month: 7
>>> d1_lst = [d1]
>>> D1 = oml.push(d1_lst)
>>> print ('type', type(D1))
>>> type <class 'oml.core.datetime.Datetime'>
>>> print ('D1:', D1)
>>> D1: [datetime.datetime(2004, 7, 24, 0, 0)]
>>> print ('year:', D1.year)
>>> year: [2004]
>>> print ('month:', D1.month)
>>> month: [7]
>>> print ('day:', D1.day)
>>> day: [24]
>>> d2 = datetime.fromisoformat('2004-07-24 00:05:23+04:00')
>>> d2_lst=[d2]
>>> D2 = oml.push(d2_lst)
>>> print('D2', D2)
>>> D2 [datetime.datetime(2004, 7, 24, 0, 5, 23, tzinfo=datetime.timezone(datetime.timedelta(seconds=14400)))]
>>> print('type', type(D2))
>>> type <class 'oml.core.datetime.Datetime'>
>>> D2.strftime()
>>> ['2004-07-24 00:05:23+04:00']
>>> d3 = "14-Jul-05 20:01:01"
>>> d3_lst = [d3]
>>> D3 = oml.push(d3_lst)
>>> oml.Datetime.strptime(D3, "DD-Mon-RR HH24:MI:SS")
>>> [datetime.datetime(2005, 7, 14, 20, 1, 1)]oml.Timedelta
oml.Timedelta objects represent a span of time, which can be used to perform simple arithmetic operations on oml.Datetime objects. The oml.Timedelta objects can be multiplied by an integer value or a floating point value. Subtracting dates creates an oml.Timedelta object that can be added, subtracted, or multiplied by a oml.Timedate object to produce another date.
Example 4-15 Using the oml.Timedelta Function
This example creates a time-based reference using the current date and time, and creates an oml.Timedelta object named DELT1 to determine a past and future dates.
import oml
import pandas as pd
import numpy as np
import datetime
from datetime import datetime, timezone, timedelta
today = datetime.now()
print('today:', today)
delt1 = timedelta(days=1, hours=2, seconds=5)
print('delt1:', delt1)
dat = pd.DataFrame({'datetime': [today], 'timedelta': [delt1]})
DAT = oml.push(dat, dbtypes = ['TIMESTAMP', 'INTERVAL DAY TO SECOND'])
TODAY = DAT['datetime']
DELT1 = DAT['timedelta']
print('TODAY:', today)
print('DELT1:', DELT1)
past_date1 = TODAY - DELT1
print('past date 1:', past_date1)
past_date2 = TODAY - (DELT1 *3)
print('past date 2:', past_date2)
future_date1 = TODAY + DELT1
print('future date 1:', future_date1)
future_date2 = TODAY + (DELT1 *3)
print('future date 2:', future_date2)
Listing for This Example
>>> import oml
>>> import pandas as pd
>>> import numpy as np
>>> import datetime
>>> from datetime import datetime, timezone, timedelta
>>> today = datetime.now()
>>> print('today:', today)
>>> today: 2022-12-27 06:00:14.555899
>>> delt1 = timedelta(days=1, hours=2, seconds=5)
>>> print('delt1:', delt1)
>>> delt1: 1 day, 2:00:05
>>> dat = pd.DataFrame({'datetime': [today], 'timedelta': [delt1]})
>>> DAT = oml.push(dat, dbtypes = ['TIMESTAMP', 'INTERVAL DAY TO SECOND'])
>>> TODAY = DAT['datetime']
>>> DELT1 = DAT['timedelta']
>>> print('TODAY:', today)
>>> TODAY: 2022-12-27 06:00:14.555899
>>> print('DELT1:', DELT1)
>>> DELT1: [datetime.timedelta(days=1, seconds=7205)]
>>> past_date1 = TODAY - DELT1
>>> print('past date 1:', past_date1)
>>> past date 1: [datetime.datetime(2022, 12, 26, 4, 0, 9, 555899)]
>>> past_date2 = TODAY - (DELT1 *3)
>>> print('past date 2:', past_date2)
>>> past date 2: [datetime.datetime(2022, 12, 23, 23, 59, 59, 555899)]
>>> future_date1 = TODAY + DELT1
>>> print('future date 1:', future_date1)
>>> future date 1: [datetime.datetime(2022, 12, 28, 8, 0, 19, 555899)]
>>> future_date2 = TODAY + (DELT1 *3)
>>> print('future date 2:', future_date2)
>>> future date 2: [datetime.datetime(2022, 12, 30, 12, 0, 29, 555899)]
oml.Integer
The oml.Integer class represents the integer data type.
Example 4-16 Using the oml.Integer Function
This example creates an oml.Integer object named INTEGER1 to represent the integer data type.
import oml
import pandas as pd
integer1 = oml.push(pd.DataFrame({'INTEGER': [0, -12, 1234, 40, 95]}), dbtypes = "NUMBER(*, 0)")
integer1Listing for This Example
>>> import oml
>>> import pandas as pd
>>> integer1 = oml.push(pd.DataFrame({'INTEGER': [0, -12, 1234, 40, 95]}), dbtypes = "NUMBER(*, 0)")
>>> integer1
INTEGER
0 0
1 -12
2 1234
3 40
4 95
Compare two oml.Datetime columns in a proxy object
using standard arithmetic comparison operators.
Example 4-17 Using value comparison Function
This example compares two oml.Datetime columns in a proxy object using standard arithmetic comparison operators.
d1 = datetime(2005, 7, 14, 5, 10, 30)
d2 = datetime(2004, 6, 30, 1, 22, 46)
d3 = datetime(2003, 12, 10, 12, 50, 25)
d4 = datetime(2002, 3, 20, 20, 42, 59)
d3 = pd.DataFrame({'X': [d1, d2], 'Y': [d3, d4]})
D3 = oml.push(d3, dbtypes = ['TIMESTAMP', 'TIMESTAMP'])
print(D3)
D4 = D3['X']
D5 = D3['Y']
print("D4:", D4)
print("D4 type:", type(D4))
print("D5:", D5)
print("D5 type:", type(D5))
print(D4 == D5)
print(D4 != D5)
print(D4 > D5)
print(D4 >= D5)
print(D4 < D5)
print(D4 <= D5)
print("max:", D3['X'].max())
print("min:", D3['Y'].min())Listing for This Example
>>> d1 = datetime(2005, 7, 14, 5, 10, 30)
>>> d2 = datetime(2004, 6, 30, 1, 22, 46)
>>> d3 = datetime(2003, 12, 10, 12, 50, 25)
>>> d4 = datetime(2002, 3, 20, 20, 42, 59)
>>> d3 = pd.DataFrame({'X': [d1, d2], 'Y': [d3, d4]})
>>> D3 = oml.push(d3, dbtypes = ['TIMESTAMP', 'TIMESTAMP'])
>>> print(D3)
>>> X Y
>>> 0 2005-07-14 05:10:30 2003-12-10 12:50:25
>>> 1 2004-06-30 01:22:46 2002-03-20 20:42:59
>>> D4 = D3['X']
>>> D5 = D3['Y']
>>> print("D4:", D4)
>>> print("D4 type:", type(D4))
>>> D4: [datetime.datetime(2005, 7, 14, 5, 10, 30), datetime.datetime(2004, 6, 30, 1, 22, 46)]
>>> D4 type: <class 'oml.core.datetime.Datetime'>
>>> print("D5:", D5)
>>> print("D5 type:", type(D5))
>>> D5: [datetime.datetime(2003, 12, 10, 12, 50, 25), datetime.datetime(2002, 3, 20, 20, 42, 59)]
>>> D5 type: <class 'oml.core.datetime.Datetime'>
>>> print(D4 == D5)
>>> [False, False]
>>> print(D4 != D5)
>>> [True, True]
>>> print(D4 > D5)
>>> [True, True]
>>> print(D4 >= D5)
>>> [True, True]
>>> print(D4 < D5)
>>> [False, False]
>>> print(D4 <= D5)
>>> [False, False] >>> [True, True] [True, True] [True, True] [False, False] [False,
>>> print("max:", D3['X'].max())
>>> max: 2005-07-14 05:10:30
>>> print("min:", D3['Y'].min())
>>> min: 2002-03-20 20:42:59Value Replacement
This function updates the elements of an oml.Datetime object, such as year, month, and day.
Example 4-18 Using value replacement function
D4 = D3['X'].replace(year=2000)
print("D4:", D4)
D5 = D3['X'].replace(month=11)
print("D5:", D5)
D6 = D3['X'].replace(day=6)
print("D6:", D6)Listing for This Example
>>> D4 = D3['X'].replace(year=2000)
>>> print("D4:", D4)
>>> D4: [datetime.datetime(2000, 7, 14, 5, 10, 30), datetime.datetime(2000, 6, 30, 1, 22, 46)]
>>> D5 = D3['X'].replace(month=11)
>>> print("D5:", D5)
>>> D5: [datetime.datetime(2005, 11, 14, 5, 10, 30), datetime.datetime(2004, 11, 30, 1, 22, 46)]
>>> D6 = D3['X'].replace(day=6)
>>> print("D6:", D6)
>>> D6: [datetime.datetime(2005, 7, 6, 5, 10, 30), datetime.datetime(2004, 6, 6, 1, 22, 46)]
Parent topic: Explore Data