Sample Exam Questions
From the objective of OSS-DB Exam Silver
S3.2 Functions and operators (Date/Time functions)
Sample Question
3.47
There is a table dt_sample created with the following SQL:
create table dt_sample(pk integer primary key, dt1 date, dt2 date, ti1 time, ts1 timestamp);
Choose three appropriate explanations for the results of the following operations on this table.
-
Executing 'select dt1 + 10 from dt_sample;' will return the date that is 10 days after the date in dt1.
-
Executing 'select dt2 - dt1 from dt_sample;' will return the number of days elapsed between dt1 and dt2 as an integer.
-
Executing 'select ti1 + 100 from dt_sample;' will return the time that is 100 seconds after the time in ti1.
-
Executing 'select ts1 + 10 from dt_sample;' will return the date and time that is 10 days after the timestamp in ts1.
-
Executing 'select dt1 + ti1 from dt_sample;' will return a timestamp that combines the date from dt1 and the time from ti1.
※This sample exam is different from those that appear in the actual OSS-DB Exam.
2024/05/02
Answer and Explanation
PostgreSQL data types related to dates and times include DATE for dates, TIME for times, TIMESTAMP for dates and times, and INTERVAL for time intervals.
Note that DATE does not include the time, and TIME does not include the date. TIME and TIMESTAMP store time with microsecond precision.
Now, for arithmetic operations on dates and times, an integer can be added or subtracted from DATE, and the integer value will be treated as the number of days. It is also possible to subtract between DATE types, and the number of days between two dates is returned as an integer.
For TIME and TIMESTAMP, you cannot add or subtract integers. For example, if you add 10 to a time, you cannot tell if it is 10 seconds, 10 minutes, or 10 days after the time. However, there are cases where you want to add or subtract from a time. In that case, you can use the INTERVAL type. For example, ti1 + interval '100 second' would be 100 seconds after ti1, and ts1 + interval '10 day' would be 10 days after ts1.
DATE and TIME can be added together, resulting in a TIMESTAMP type.
So the correct answers are A, B, and E.
© EDUCO All Rights Reserved.