Что такое timestamp в sql

What is TIMESTAMP() in SQL?

Timestamp in Structured Query Language (SQL) is a data type, that represents the date and time value of any specific record at any specific time (or event). SQL Timestamps can store and work with dates, times, time zones, and AD/BCs.

The Timestamp in SQL allows us to store and perform our operations with both the dates and time values, and that too without any particular time zone usually.

The TIMESTAMP() function in SQL allows us to return any DateTime expression, from any date or any DateTime expression. The TIMESTAMP() function also accepts any date expression and adds time value to it, finally returning the overall DateTime expression.

A timestamp data type can be configured in some SQL databases so that when the database is used in a different time zone, the corresponding date and time are displayed and accepted. One such example is the TIMESTAMPZ data type in PostgreSQL.

Now that we have got a brief idea about the timestamp in SQL let us look at the syntax of the same.

Syntax of TIMESTAMP() function in SQL

There are different syntaxes for the timestamp function in SQL. Any valid TimeStamp data expressions generally consist of a date and time, followed by BC or AD as an option. Let us understand it through the syntax below.

Syntax:

In the above syntax, the TIMESTAMP() function in SQL takes the time or the DateTime expression as a parameter. It returns the DateTime value of the expression (or date) as the output.

Let us look at another type of syntax of the TIMESTAMP() function in SQL.

Syntax:

The TIMESTAMP() function not only accepts the DateTime expression as the input but can even access two date or DateTime values as the arguments. After taking two date-times as input, it adds the first argument to the second and then returns the resultant DateTime value.

Now that we have got a brief idea about the syntax of timestamp in SQL , let us look at the parameter values of the same.

Parameters Value

1. Expression: The expression is a mandatory parameter in the TIMESTAMP() function in SQL. The expression can either be a date or a DateTime expression.
2. Time: The time is an optional parameter in the TIMESTAMP() function in SQL. It is a kind of time value that is added to the expression.

Examples of the Timestamp in SQL

Let us now look at some examples to better understand the TIMESTAMP() function in SQL.

Example 1: Return a DateTime Value Based on the Arguments:

We will pass a DateTime value to our TIMESTAMP() function in this example. Let us see what it returns.

Code:

Output:

Explanation:

We passed a date expression in the above code using the SELECT statement to our timestamp() function.

The TIMESTAMP() function will basically add the default time expression to the timestamp and then return the resultant value.

Example 2: Checking the Current TimeStamp

Code:

Output:

Explanation:

So, we can know the current timestamp by using the current_timestamp command.

Example 3: Extracting Information from Timestamp

There are a few functions in SQL, for example, EXTRACT , which allows us to extract some particular set of information from the timestamp. For instance, we can extract seconds, minutes, hours, days, months, years, etc., from the timestamp.

Let us look at the code for the same.

Code:

Output:

Explanation:

In the above code, we have extracted the day from the timestamp. We have used the EXTRACT keyword for the same.

To extract the month, we can write the below code :

Code:

Output:

Explanation: In the above code, we have extracted the month from the timestamp. We have used the EXTRACT keyword for the same.

Now that we have seen about various examples of TIMESTAMP, let us look at Timestamp vs. Timestampz.

TIMESTAMP VS TIMESTAMPZ

Timestamp and Timestampz have a minor difference. The TIMESTAMPZ can be called similar to TIMESTAMP, but with a TIME ZONE. So, the TIMESTAMPZ has a time zone associated with it. Other than that, both the data types stores the UTC offset of the specified times.

Certain SQL databases allow the modification of a timestamp data type. In these databases, we can mention a timezone for the timestamp. By doing that, whenever the database is used in a different time zone, the corresponding date and time will be displayed and accepted. For the same reason, TIMESTAMPZ is used.

Note: TIMESTAMPZ is a data type in PostgreSQL

Learn More About a Time and Date Format in SQL

You can learn more about the time and date format in SQL by reading the following article on Scaler Topics:

Conclusion

In this article, we learned about SQL's TIMESTAMP() function. Let us quickly summarize what we have learned till now.

Datetime или timestamp

На днях я столкнулся с тем, что многие разработчики не знают в чём отличие типов данных DATETIME и TIMESTAMP в MySQLе, а так же как хранить дату и время, если необходимо учитывать разные часовые пояса для разных пользователей веб-приложения. Поэтому хочу дать ниже разъяснения с пояснениями.

DATETIME
Хранит время в виде целого числа вида YYYYMMDDHHMMSS, используя для этого 8 байтов. Это время не зависит от временной зоны. Оно всегда отображается при выборке точно так же, как было сохранено, независимо от того какой часовой пояс установлен в MySQL. Даю пример:

mysql> create table `dt1` ( col datetime NOT NULL );
mysql> SET @@session.time_zone=’+00:00′;
mysql> select now();
+———————+
| now() |
+———————+
| 2009-06-04 18:13:56 |
+———————+

mysql> insert into dt1 values(now());

mysql> insert into dt1 values(now());

TIMESTAMP
Хранит 4-байтное целое число, равное количеству секунд, прошедших с полуночи 1 января 1970 года по усреднённому времени Гринвича (т.е. нулевой часовой пояс, точка отсчёта часовых поясов). При получении из базы отображается с учётом часового пояса. Часовой пояс может быть задан в операционной системе, глобальных настройках MySQL или в конкретной сессии. Запомните, что сохраняется всегда количество секунд по UTC (универсальное координированное время, солнечное время на меридиане Гринвича), а не по локальному часовому поясу. Пример:

Ещё одно отличие! TIMESTAMP по умолчанию NOT NULL, а его значение по умолчанию равно NOW().

mysql> insert into dt1 values(null);
ERROR 1048 (23000): Column ‘col’ cannot be null
mysql> insert into tm1 values(null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tm1;
+———————+
| col |
+———————+
| 2009-06-04 18:25:08 |
| 2009-06-04 18:25:26 |
| 2009-06-04 18:32:50 |
+———————+

Что такое timestamp в sql

Типы DATETIME , DATE и TIMESTAMP являются родственными типами данных. В данном разделе описаны их свойства, общие черты и различия.

Тип данных DATETIME используется для величин, содержащих информацию как о дате, так и о времени. MySQL извлекает и выводит величины DATETIME в формате ‘YYYY-MM-DD HH:MM:SS’ . Поддерживается диапазон величин от ‘1000-01-01 00:00:00’ до ‘9999-12-31 23:59:59’ . (»поддерживается» означает, что хотя величины с более ранними временными значениями, возможно, тоже будут работать, но нет гарантии того, что они будут правильно храниться и отображаться).

Тип DATE используется для величин с информацией только о дате, без части, содержащей время. MySQL извлекает и выводит величины DATE в формате ‘YYYY-MM-DD’ . Поддерживается диапазон величин от ‘1000-01-01’ до ‘9999-12-31’ .

Тип столбца TIMESTAMP обеспечивает тип представления данных, который можно использовать для автоматической записи текущих даты и времени при выполнении операций INSERT или UPDATE . При наличии нескольких столбцов типа TIMESTAMP только первый из них обновляется автоматически.

Автоматическое обновление первого столбца с типом TIMESTAMP происходит при выполнении любого из следующих условий:

• Столбец не указан явно в команде INSERT или LOAD DATA INFILE .
• Столбец не указан явно в команде UPDATE , и при этом изменяется величина в некотором другом столбце (следует отметить, что команда UPDATE , устанавливающая столбец в то же самое значение, которое было до выполнения команды, не вызовет обновления столбца TIMESTAMP , поскольку в целях повышения производительности MySQL игнорирует подобные обновления при установке столбца в его текущее значение).
• Величина в столбце TIMESTAMP явно установлена в NULL .

Для остальных (кроме первого) столбцов типа TIMESTAMP также можно задать установку в значение текущих даты и времени. Для этого необходимо просто установить столбец в NULL или в NOW() .

Любой столбец типа TIMESTAMP (даже первый столбец данного типа) можно установить в значение, отличное от текущих даты и времени. Это делается путем явной установки его в желаемое значение. Данное свойство можно использовать, например, если необходимо установить столбец TIMESTAMP в значение текущих даты и времени при создании строки, а при последующем обновлении этой строки значение столбца не должно изменяться:

• Пусть MySQL автоматически установит значение столбца с типом TIMESTAMP при создании данной строки. Столбец будет установлен в исходное состояние со значением текущих даты и времени.
• При выполнении последующих обновлений других столбцов в данной строке необходимо явно установить столбец TIMESTAMP в его текущее значение.

Однако, с другой стороны, для этих целей, возможно, будет проще использовать столбец DATETIME . При создании строки его следует инициализировать функцией NOW() и оставить в покое при последующих обновлениях.

Величины типа TIMESTAMP могут принимать значения от начала 1970 года до некоторого значения в 2037 году с разрешением в одну секунду. Эти величины выводятся в виде числовых значений.

Формат данных, в котором MySQL извлекает и показывает величины TIMESTAMP , зависит от количества показываемых символов. Это проиллюстрировано в приведенной ниже таблице. Полный формат TIMESTAMP составляет 14 десятичных разрядов, но можно создавать столбцы типа TIMESTAMP и с более короткой строкой вывода:

Тип столбца	Формат вывода
TIMESTAMP(14)	YYYYMMDDHHMMSS
TIMESTAMP(12)	YYMMDDHHMMSS
TIMESTAMP(10)	YYMMDDHHMM
TIMESTAMP(8)	YYYYMMDD
TIMESTAMP(6)	YYMMDD
TIMESTAMP(4)	YYMM
TIMESTAMP(2)	YY

Независимо от размера выводимого значения размер данных, хранящихся в столбцах типа TIMESTAMP , всегда один и тот же. Чаще всего используется формат вывода с 6, 8, 12 или 14 десятичными знаками. При создании таблицы можно указать произвольный размер выводимых значений, однако если этот размер задать равным 0 или превышающим 14, то будет использоваться значение 14. Нечетные значения размеров в интервале от 1 до 13 будут приведены к ближайшему большему четному числу.

Величины DATETIME , DATE и TIMESTAMP могут быть заданы любым стандартным набором форматов:

• Как строка в формате ‘YYYY-MM-DD HH:MM:SS’ или в формате ‘YY-MM-DD HH:MM:SS’ . Допускается «облегченный» синтаксис — можно использовать любой знак пунктуации в качестве разделительного между частями разделов даты или времени. Например, величины ’98-12-31 11:30:45′ , ‘98.12.31 11+30+45′ , ’98/12/31 11*30*45′ и ’98@12@31 11^30^45’ являются эквивалентными.
• Как строка в формате ‘YYYY-MM-DD’ или в формате ‘YY-MM-DD’ . Здесь также допустим «облегченный» синтаксис. Например, величины ’98-12-31′ , ‘98.12.31’ , ’98/12/31′ и ’98@12@31′ являются эквивалентными.
• Как строка без разделительных знаков в формате ‘YYYYMMDDHHMMSS’ или в формате ‘YYMMDDHHMMSS’ , при условии, что строка понимается как дата. Например, величины ‘19970523091528’ и ‘970523091528’ можно интерпретировать как ‘1997-05-23 09:15:28’ , но величина ‘971122129015’ является недопустимой (значение раздела минут является абсурдным) и преобразуется в ‘0000-00-00 00:00:00’ .
• Как строка без разделительных знаков в формате ‘YYYYMMDD’ или в формате ‘YYMMDD’ , при условии, что строка интерпретируется как дата. Например, величины ‘19970523’ и ‘970523’ можно интерпретировать как ‘1997-05-23’ , но величина ‘971332’ является недопустимой (значения разделов месяца и дня не имеют смысла) и преобразуется в ‘0000-00-00’ .
• Как число в формате YYYYMMDDHHMMSS или в формате YYMMDDHHMMSS , при условии, что число интерпретируется как дата. Например, величины 19830905132800 и 830905132800 интерпретируются как ‘1983-09-05 13:28:00’ .
• Как число в формате YYYYMMDD или в формате YYMMDD , при условии, что число интерпретируется как дата. Например, величины 19830905 и 830905 интерпретируются как ‘1983-09-05’ .
• Как результат выполнения функции, возвращающей величину, приемлемую в контекстах типов данных DATETIME , DATE или TIMESTAMP (например, функции NOW() или CURRENT_DATE ).

Недопустимые значения величин DATETIME , DATE или T IMESTAMP преобразуются в значение «ноль» соответствующего типа величин ( ‘0000-00-00 00:00:00’ , ‘0000-00-00’ , или 00000000000000 ).

Для величин, представленных как строки, содержащие разделительные знаки между частями даты, нет необходимости указывать два разряда для значений месяца или дня, меньших, чем 10 . Так, величина ‘1979-6-9’ эквивалентна величине ‘1979-06-09’ . Аналогично, для величин, представленных как строки, содержащие разделительные знаки внутри обозначения времени, нет необходимости указывать два разряда для значений часов, минут или секунд, меньших, чем 10 . Так,

Величины, определенные как числа, должны иметь 6 , 8 , 12 , или 14 десятичных разрядов. Предполагается, что число, имеющее 8 или 14 разрядов, представлено в форматах YYYYMMDD или YYYYMMDDHHMMSS соответственно, причем год указан в первых четырех разрядах. Если же длина числа 6 или 12 разрядов, то предполагаются соответственно форматы YYMMDD или YYMMDDHHMMSS , где год указан в первых двух разрядах. Числа, длина которых не соответствует ни одному из описанных вариантов, интерпретируются как дополненные спереди нулями до ближайшей вышеуказанной длины.

Величины, представленные строками без разделительных знаков, интерпретируются с учетом их длины согласно приведенным далее правилам. Если длина строки равна 8 или 14 символам, то предполагается, что год задан первыми четырьмя символами. В противном случае предполагается, что год задан двумя первыми символами. Строка интерпретируется слева направо, при этом определяются значения для года, месяца, дня, часов, минут и секунд для всех представленных в строке разделов. Это означает, что строка с длиной меньше, чем 6 символов, не может быть использована. Например, если задать строку вида ‘9903’ , полагая, что это будет означать март 1999 года, то MySQL внесет в таблицу «нулевую» дату. Год и месяц в данной записи равны 99 и 03 соответственно, но раздел, представляющий день, пропущен (значение равно нулю), поэтому в целом данная величина не является достоверным значением даты.

При хранении допустимых величин в столбцах типа TIMESTAMP используется полная точность, указанная при их задании, независимо от количества выводимых символов. Это свойство имеет несколько следствий:

• Необходимо всегда указывать год, месяц и день даже для типов TIMESTAMP(4) или TIMESTAMP(2) . В противном случае задаваемая величина не будет допустимым значением даты и будет храниться как 0 .
• При увеличении ширины узкого столбца TIMESTAMP путем использования команды ALTER TABLE будет выводиться ранее «скрытая» информация.
• И аналогично, при сужении столбца TIMESTAMP хранимая информация не будет потеряна, если не принимать во внимание, что при выводе информации будет выдаваться меньше.
• Хотя величины TIMESTAMP хранятся с полной точностью, непосредственно может работать с этим исходным хранимым значением величины только функция UNIX_TIMESTAMP() . Остальные функции оперируют форматированными значениями извлеченной величины. Это означает, что нельзя использовать такие функции, как HOUR() или SECOND() , пока соответствующая часть величины TIMESTAMP не будет включена в ее форматированное значение. Например, раздел HH столбца TIMESTAMP не будет выводиться, пока количество выводимых символов не станет по меньшей мере равным 10 , так что попытки использовать HOUR() для более коротких величин TIMESTAMP приведут к бессмысленным результатам.

Величины одного типа даты можно в ряде случаев присвоить объекту другого типа даты. Однако при этом возможны некоторое изменение величины или потеря информации:

• Если присвоить значение типа DATE объекту DATETIME или TIMESTAMP , то в результирующей величине «временная» часть будет установлена в ’00:00:00′ , так как величина DATE не содержит информации о времени.
• Если присвоить значение типа DATE , DATETIME или TIMESTAMP объекту DATE , то «временная» часть в результирующей величине будет удалена, так как тип DATE не включает информацию о времени.
• Несмотря на то что все величины DATETIME , DATE и TIMESTAMP могут быть указаны с использованием одного и того же набора форматов, следует помнить, что указанные типы имеют разные интервалы допустимых значений. Например, величины типа TIMESTAMP не могут иметь значения даты более ранние, чем относящиеся к 1970 году или более поздние, чем относящиеся к 2037 году. Это означает, что такая дата, как ‘1968-01-01’ , будучи разрешенной для величины типа DATETIME или DATE , недопустима для величины типа TIMESTAMP и будет преобразована в 0 при присвоении этому объекту.

Задавая величины даты, следует иметь в виду некоторые «подводные камни»:

SQL Timestamp

By Priya Pedamkar

Introduction to SQL Timestamp

Timestamp is a data type and function in Standard Structured Query Language (SQL) that lets us store and work with both date and time data values, usually without specified time zones. The timestamp has a storage size of 8 bytes that can accept date and time values ranging from 4713 BC and 294276 AD and provides a resolution of 1 microsecond or 14 digits. Some SQL databases allow for customization of a timestamp data type where we can specify a timezone for the timestamp so that every time the database is used in a different timezone, it shows and accepts the corresponding date and time. For example, in PostgreSQL, we have a “timestamptz” data type that takes care of time zone changes as well. Timestamptz data type also has a storage size similar to a timestamp of 8 bytes that can accept date and time values ranging from 4713 BC and 294276 AD and provides a resolution of 1 microsecond or 14 digits.

In this article, we will be learning the functions and differences of “timestamp” and “timestamptz” data types with the help of a few examples.

Hadoop, Data Science, Statistics & others

Syntax and Parameters:

The basic syntax of “timestamp” data type in SQL is as follows :

Timestamp ‘date_expression time_expression’;

A valid timestamp data expression consists of a date and a time, followed by an optional BC or AD.

Timestamptz ‘date_expression time_expression +/- time_zone_expression ’;
2004-10-19 10:23:54;

In this case, a valid timestamp data expression consists of a date and a time, followed by a time_zone expression concatenated with a ‘+/-’ sign based on the position of the time zone with respect to ‘GMT’ and finally followed by an optional BC or AD.

Examples to Implement of SQL Timestamp

Below are a few examples to understand SQL Timestamp data type:

Example #1

Getting to know important details of timestamp and timestamptz data type in SQL.

Code:

SELECT typname, typlen, typtype, typcategory
FROM pg_type
WHERE typname

Example #2

SQL query to illustrate the difference between timestamp and timestamptz data types.

Code:

SELECT TIMESTAMP ‘2020-03-23 00:00’;

SELECT TIMESTAMPTZ ‘2020-03-23 00:00’;

Example #3

Some examples to show functions related to timestamp data type in SQL. First, let’s start by converting or casting a given date into a timestamp format, as shown below.

Code:

SELECT ‘2020-03-23’ :: timestamptz;

Suppose if we want to know the current timestamp, we can use the current_timestamp function as shown below. It will return the current timestamp, i.e. current date and time implicitly converted into the default timezone.

Further, we can also check the timezone we are currently working in by using the SHOW timezone statement below. This becomes very helpful when you have operations all over the world.

Next, we can even change the current timezone to a different timezone using the SET timezone statement. Here, you may choose from a wide variety of timezones like ‘Asia/Kolkata’, ‘Europe/Zurich’, ‘US/Pacific’ etc. In this example, we have changed the timezone from ‘Asia/Kolkata’ to ‘US/Pacific’.

SET timezone = ‘US/Pacific’;

A few functions like EXTRACT in SQL let us extract a specific piece of information from the timestamp. For example, we can extract DAY, MONTH, YEAR, HOUR, MINUTE, SECONDS, etc., from the timestamp.

In the following examples, we have tried to extract DAY and MONTH from the timestamp.

SELECT EXTRACT(DAY FROM ‘2020-03-23 00:00’:: timestamp);

SELECT EXTRACT(MONTH FROM ‘2020-03-23 00:00’:: timestamp);

Example #4

SQL query to generate a series of timestamps in a specified timezone. In this example, we have generated a series of timestamps with a regular interval of 5 hours in two time zones, namely, ‘Indian Standard Time’ and ‘Pacific Standard Time. We can observe the difference between the two series by closely looking at the outputs.

Code:

SELECT generate_series(
timezone(‘IST’, ‘2020-03-23 00:00’::timestamp),
timezone(‘IST’, ‘2020-03-23 23:00’::timestamp),
‘5 hour’::interval
);

SELECT generate_series(
timezone(‘PST’, ‘2020-03-23 00:00’::timestamp),
timezone(‘PST’, ‘2020-03-23 23:00’::timestamp),
‘5 hour’::interval
);

Example #5

A practical example to illustrate the use of timestamp or timestamptz data type in SQL. Let’s take an example of a business case, where a multinational bank wants to wish its customers “Happy Birthday” based on the customer’s local time. The bank’s database has a list of customers with their date of births. So the customers’ table can be created something like this :

Code:

CREATE TABLE Customers (
customer_id int,
customer_name varchar(255),
city varchar(255),
date_of_birth timestamptz
);

After inserting the relevant information, the data in the table will look something like this:

Suppose we want to wish our customers in London who have their birthdays today i.e. ‘24th March 2020’ as per Indian Standard Time; we might be required to write a SQL query as shown below:

SELECT customer_name, date_of_birth
FROM customers
WHERE EXTRACT(DAY FROM date_of_birth) = ’24’
AND EXTRACT(MONTH FROM date_of_birth) = ’03’;

But in the above example, we can see that since the customer is from London and it’s not 24th March there, the first query might not be the best option. So we can improve the query by mentioning the time zone as ‘GMT’, as mentioned in the query below.

SELECT customer_name, date_of_birth
FROM customers
WHERE EXTRACT(DAY FROM date_of_birth at time zone ‘GMT’) = ’24’
AND EXTRACT(MONTH FROM date_of_birth at time zone ‘GMT’) = ’03’;

Conclusion

SQL timestamp is a data type and function used to store and work with data values in date and time formats, sometimes along with time zones and AD/BCs. They are very helpful when we have operations all over the world.

Recommended Articles

This is a guide to SQL Timestamp. Here we discuss the Introduction to SQL Timestamp and the practical examples and different subquery expressions. You can also go through our suggested articles to learn more –