Recipe9.3.Extracting Units of Time from a Date

Recipe 9.3. Extracting Units of Time from a Date

Problem

You want to break the current date down into six parts: day, month, year, second, minute, and hour. You want the results to be returned as numbers.

Solution

My use of the current date is arbitrary. Feel free to use this recipe with other dates. In Chapter 1, I mention the importance of learning and taking advantage of the built-in functions provided by your RDBMS; this is especially true when it comes to working with dates. There are different ways of extracting units of time from a date than those presented in this recipe, and it would benefit you to experiment with different techniques and functions.

DB2

DB2 implements a set of built-in functions that make it easy for you to extract portions of a date. The function names HOUR, MINUTE, SECOND, DAY, MONTH, and YEAR conveniently correspond to the units of time you can return: if you want the day use DAY, hour use HOUR, etc. For example:

  1 select    hour( current_timestamp ) hr, 2         minute( current_timestamp ) min, 3         second( current_timestamp ) sec, 4            day( current_timestamp ) dy, 5          month( current_timestamp ) mth, 6            year( current_timestamp ) yr 7   from t1   HR   MIN   SEC    DY   MTH    YR ---- ----- ----- ----- ----- -----   20    28    36    15     6  2005 

Oracle

Use functions TO_CHAR and TO_NUMBER to return specific units of time from a date:

  1  select to_number(to_char(sysdate,'hh24')) hour, 2         to_number(to_char(sysdate,'mi')) min, 3         to_number(to_char(sysdate,'ss')) sec, 4         to_number(to_char(sysdate,'dd')) day, 5         to_number(to_char(sysdate,'mm')) mth, 6         to_number(to_char(sysdate,'yyyy')) year 7   from dual   HOUR   MIN   SEC   DAY   MTH  YEAR   ---- ----- ----- ----- ----- -----     20    28    36    15     6  2005 

PostgreSQL

Use functions TO_CHAR and TO_NUMBER to return specific units of time from a date:

  1 select to_number(to_char(current_timestamp,'hh24'),'99') as hr, 2        to_number(to_char(current_timestamp,'mi'),'99') as min, 3        to_number(to_char(current_timestamp,'ss'),'99') as sec, 4        to_number(to_char(current_timestamp,'dd'),'99') as day, 5        to_number(to_char(current_timestamp,'mm'),'99') as mth, 6        to_number(to_char(current_timestamp,'yyyy'),'9999') as yr 7   from t1    HR   MIN   SEC   DAY   MTH    YR  ---- ----- ----- ----- ----- -----  20      28    36    15     6  2005 

MySQL

Use the DATE_FORMAT function to return specific units of time from a date:

  1 select date_format(current_timestamp,'%k') hr, 2        date_format(current_timestamp,'%i') min, 3        date_format(current_timestamp,'%s') sec, 4        date_format(current_timestamp,'%d') dy, 5        date_format(current_timestamp,'%m') mon, 6        date_format(current_timestamp,'%Y') yr 7   from t1   HR   MIN   SEC   DAY   MTH    YR ---- ----- ----- ----- ----- -----   20    28    36    15     6  2005 

SQL Server

Use the function DATEPART to return specific units of time from a date:

  1 select datepart( hour, getdate()) hr, 2        datepart( minute,getdate()) min, 3        datepart( second,getdate()) sec, 4        datepart( day, getdate()) dy, 5        datepart( month, getdate()) mon, 6        datepart( year, getdate()) yr 7   from t1   HR   MIN   SEC   DAY   MTH    YR ---- ----- ----- ----- ----- -----   20    28    36    15     6  2005 

Discussion

There's nothing fancy in these solutions; just take advantage of what you're already paying for. Take the time to learn the date functions available to you. This recipe only scratches the surface of the functions presented in each solution. You'll find that each of the functions takes many more arguments and can return more information than what this recipe provides you.