The Experts below are selected from a list of 12 Experts worldwide ranked by ideXlab platform

Joe Celko - One of the best experts on this subject based on the ideXlab platform.

  • 38 – Temporal Queries
    Joe Celko's SQL for Smarties, 2011
    Co-Authors: Joe Celko
    Abstract:

    Publisher Summary This chapter focuses on the use of temporal queries in SQL. Almost every SQL implementation has a DATE data type and the most Common ones are a constructor that builds a date from integers or strings; extractors that pull out the month, day, or year; and some display options that format output. In Standard SQL, the constructor is CAST ( AS [DATE | TIME | TIMESTAMP]) for a string expression. The only ISO 8601 format allowed for datetime values are “yyyy-mm-dd” for dates, “hh:mm:ss.sssss” for times, and “yyyy-mm-dd hh:mm:ss.sssss” for timestamps, with the number of decimal places being implementation-defined. The primary datetime fields other than SECOND contain nonnegative integer values, constrained by the natural rules for dates using the Common Era Calendar. SECOND, however, can be defined to have a that indicates the number of decimal digits maintained following the decimal point in the seconds value. One of the major problems faced in the real world is how to handle a series of events that occur in the same time period or in some particular order. The best way to do this is in the DDL so that unwanted gaps and overlaps never get into the database in the first place.

  • 8 – Auxiliary Tables
    Joe Celko's SQL for Smarties, 2011
    Co-Authors: Joe Celko
    Abstract:

    Publisher Summary This chapter focuses on auxiliary tables that are a way of building functions and look-up tables that would be difficult if not impossible to do with the limited computational power of SQL. Auxiliary tables serve as adjuncts to do queries via joins rather than computations and they are usually very static and constructed from an outside data source. Thus they do not require the same constraint for safety and they need to have a primary key declared so that it creates a fast access method for searching and joining the auxiliary table to other tables in the schema. The most important auxiliary table is a Calendar as the Common Era Calendar is too irregular for easy computations. The Series table is a simple list of integers from 1 to (n) that is used in place of looping constructs in a procedural language. The most Common form of look-up tables has two columns, one for the value to be looked up and one for the translation of that value into something the user needs. Although most auxiliary look-up tables are meant for simple decoding, they can also be used for more complex functions such as programming the Net Present Value (NPV) and its related Internal Rate of Return (IRR) in SQL.

  • Chapter 41 – Temporal Data
    Joe Celko's Data Measurements and Standards in SQL, 2010
    Co-Authors: Joe Celko
    Abstract:

    Publisher Summary This chapter focuses on the temporal data types used in Standard SQL. Standard SQL has rules for converting the numeric and character strings into these data types and there is a schema information table for global time zone information to make sure that these data types are synchronized. As an international standard, Standard SQL has to handle time for the whole world. All SQL implementations have a DATE data type, whereas most have a separate TIME and a TIMESTAMP data type. These values are drawn from the system clock and are therefore local to the host machine. They are based on what is now called the Common Era Calendar, which many people would still call the Gregorian or Christian Calendar. Standard SQL has a set of date and time and INTERVAL data types. Both of these groups are temporal data types, but datetimes represent points in the time line, while the interval data types are durations of time. Standard SQL also has a full set of opErators for these data types. There are two ways of representing temporal data internally. The “Unix representation” is based on keeping a single long integer or a word of 64 or more bits that counts the computer clock ticks from a base starting date and time. The other representation is called the “COBOL method,” which uses separate fields for the year, month, day, hours, minutes, and seconds.

  • Chapter 7 – Dates
    Joe Celko's Data Measurements and Standards in SQL, 2010
    Co-Authors: Joe Celko
    Abstract:

    Publisher Summary National date formats may be confusing when used in an international environment. This chapter focuses on the ISO-8601 standard used for dates and times, which uses Common Era (CE) dates, time of day, combined date and time of day, and time intervals. The year has at least four digits and 1875-05-20 has been fixed as a reference Calendar date to the Common Era Calendar. The month values are shown as two digits in the range of 01–12 where the month is not used by itself but has to have a year attached to it. The format for the month is “YYYY-MM,” and MySQL has an extension to this that uses “YYYY-MM-00,” where the zeros are a placeholder for an unspecified day within the month or the whole month. YYYY indicates the ISO week-numbering year, which is slightly different from the Calendar year. Www is the week number prefixed by the letter “W,” starting from W01 to W53. The ISO standard allows both the “YYYY-MM-DD” and “YYYYMMDD” formats for complete Calendar date representations. The SQL standards use the YYYY-MM-DD format, but vendors may allow other formats, usually national variants. The format of the ordinal date is “YYYY-DDD” or “YYYYDDD,” where DDD maps from 001 to 365 or 366 in leap years. This format is usually embedded in encoding schemes that depend on a timestamp of some kind. ISO 8601 uses the 24-hour clock system where a day starts at “00:00:00” (zero hour) and ends just before the start of the following day. Hours are two digits between 00 and 23, where “24” is used as the “00” of the following day.