What is a timestamp in DBMS

SQL extract - Access to date and time fields

SQL enables access to individual components of a temporal data type (,,,).

SQL uses the keyword to separate the time component name from the value:

The field names are also SQL keywords - you must not put them in single or double quotes.

SQL returns an exact numeric value. If the field is extracted, the value can also contain decimal places. 0 The following table shows the field names provided by the SQL standard.

importance Field name
year
month
Day of the month
Hour (0-23)
minute
Seconds (including fraction)
Time zone: hour
Time zone: minute

Related functions

can only extract a single field at a time. To extract a complete date (year, month, day) or a complete time (hour, minute, second) from a value, you can use: 1

This is particularly useful in the clause. However, one should be careful in the clause: See "Inappropriate use in the clause“Below.

Caution: Oracle database

The Oracle database does not have a predefined untimed date type. Even the Oracle data type has the time components - in this respect, the Oracle data type corresponds to the standard type .2

A type conversion () to therefore does not discard the time component in the Oracle database.

In order to only take into account the date - without the time component - the proprietary function is often used in the Oracle database to set the time fields to zero ():

Notice that the result still has time components - they are just zero (). The effect is essentially like the following standard SQL:

compatibility

SQL already existed in SQL-92 (intermediate) and is now part of the optional feature F052, “Intervals and datetime arithmetic”. Despite its age and relevance, it is not yet supported by all common databases.

Common mistakes

String formatting

A common mistake is to use string formatting functions (e.g.) instead of extracting individual date or time fields. These functions often apply unexpected formatting rules: E.g. leading spaces or zeros or period () instead of comma () as decimal separator depending on the current environment (locale).

This environment-dependent behavior can lead to errors that are not expressed everywhere and are therefore difficult to fix.

Inappropriate use in the clause

Consider the following anti-pattern:

This anti-pattern is often used so that the “last moment” of the relevant time range does not have to be specified explicitly. It is actually an important and worthwhile goal, since the "last moment" of a time range cannot always be determined:

Time units are inconsistent

It is common knowledge that different months are of different lengths. Even the rules for leap years are at least partially known. So far, the “last moment” can be determined algorithmically.

But there are also leap seconds that are irregular. They are inserted occasionally (approx. Every 18 months) if necessary. For example, the last second of 2016 in UTC was 23:59:60 (or 00:59:60 on January 1st, 2017 in CET). So if you assume a year ends at 11:59:59 pm, you could be missing a second

Since the leap seconds are irregular and are announced at relatively short notice - the leap second 2016 was announced less than six months in advance - it is generally impossible to predict the “last moment” for more than six months.

In addition to this, more or less theoretical special case, the calculation of the “last moment” is of course laborious and should be avoided for that reason alone.

The resolution of the time is unknown (at least in the future)

Even if you correctly determine the last day and the last second of a period of time, you still have to enter enough decimal places to correctly specify the "last moment". If you know that the corresponding data type does not allow any decimal places (e.g.), you do not have to enter any decimal places. However, if the data type is later changed (e.g. to), hardly anyone will look for the “last minute” assumptions and add the necessary number of 9's if necessary.

It is therefore generally good practice to do without specifying the “last moment” of a time range. , and string formatting in the clause are usually the wrong way to achieve the right goal.

The following clause is equivalent to the example above, but still avoids having to specify the "last moment" of 2016:

Follow the pattern: Use an inclusive condition () for the lower limit, but an exclusive condition () for the upper limit. The upper limit must therefore be the first moment that locked out shall be. The inclusive / exclusive pattern doesn't need the “last moment” because instead it uses the less problematic first moment twice.

Note that SQL’s include both limit values ​​and therefore cannot be used for the inclusive / exclusive pattern.

Compared to the solution, the inclusive / exclusive condition has two advantages:

Applicable to all time ranges

A single day, month, ... can easily be delimited - even if the time range is not based on a calendar day, month, ....

To clarify, try to implement the following clause or something similar: 4

Easier to index

An index on is hardly useful if this column only appears in the clause in an expression such as.5 The inclusive / exclusive pattern can use this index without any problems. More about indexing on Use The Index, Luke!

Proprietary extension: additional fields

Some databases offer additional fields. The following table lists the most common ones. Please note, however, that these are extensions by the manufacturer: You can therefore behave differently with different data beacons. The field works in three tested databases, but gives a different result for each.

Proprietary alternatives

Practically all databases offer the possibility of extracting individual components from date or time values ​​(,,). For those cases in which the standard expression does not work, a description of the corresponding proprietary alternatives follows.

: SQL Server

Microsoft SQL Server offers the proprietary function. The following example is equivalent to.

The result value is always of the type. The fractions of a second can be extracted using separate field names (e.g.).

The following expression is equivalent for up to 9 decimal places to

See “DATEPART (Transact-SQL)” for a list of all available field names.

strftime - SQLite

SQLite offers the proprietary function for formatting dates and times.6 To extract a single field, format this field as a character string and, if necessary, perform a type conversion () to a numeric data type.

The following example implements in SQLite:

Note that the format string (for seconds) does not provide any decimal places. Instead, use (seconds with three decimal places):

extract (second_microsecond…) - MySQL, MariaDB

MySQL’s and MariaDB’s only deliver integer values. To get the seconds with fractions one can use the proprietary field:

About the author

Markus Winand is the SQL Renaissance Ambassador on a mission to alert developers to the evolution of SQL in the 21st century. Markus can be hired as a trainer, speaker and consultant on winand.at.

Footnotes