In the modern day, the usage of the
word timestamp has expanded and is usually used in reference to the
digital time and date that are attached to certain information
related to any sort of digital data. So, the timestamp is basically a
sequence of encoded information or characters that help in
identifying the time of the occurrence of a certain event. A
timestamp usually includes the date and time, but sometimes the
timestamps are accurate so as to give the information about the
millisecond too.

Hence, the computer records the
current time of the occurrence of each event and the accuracy of the
current time is maintained through mechanisms like Network Time
Protocol (NTP) which calibrates the current time to minute fractions
of a second. This precision makes it viable for applications and
computers with networks to work and communicate effectively.

The mechanism of time stamp is
mainly required for numerous synchronization purposes.

For example, in case of a
multi-event transaction, timestamps are used to assign sequences so
that in case of a failure, the transaction can be stopped.

Timestamps are also used for
recording time in relation to particular starting points.

For example, in IP telephony
the RTP or the real time protocol assigns timestamps in sequence to
voice packets. This is turn enables the user to buffer, reassemble
and deliver without any sort of error.

When programmers write
programs, they are usually provided with an application program
interface so that the operating systems can provide a timestamp for
a program when it is being executed.

Timestamps are also required
for tracking progress of different records over time. Accurate
timestamps enable accurate comparisons.

The process in
which actual data has timestamps recorded, is known as time-stamping.

What is UNIX
time stamp?

Before we can
start with what UNIX time is, it is essential to clarify what Unix
Epoch is. Epoch is a term used in the field of chronology and is
defined as an instant in time, which is chosen as the origin of a
given particular era. So the UNIX epoch is January 1st
1970. Now the UNIX time stamp is a way in which time is tracked as a
running total of seconds and this count has it's beginning from the
UNIX epoch i.e. January, the first of 1970. So, the UNIX timestamp is
the number of seconds between a date that we need to look up or mark
and the UNIX epoch. This method of time stamping is very essential
for computer systems to sort and date the information which is
present in distributed and dynamic applications both online as well
as on the client's side.

Timestamp
formats

There are many
timestamp formats that are commonly used. Log analysis by certain IT
companies is capable of annotating a huge number of these timestamp
formats. Time stamp annotations are also called date-time annotations
and are constructed using two base formats-

A date format

A time format

The date-time annotations
work in the following manner-

The date patterns are
identified by an annotator in a text and are then annotated with
date annotations.

The same process is repeated
with time annotation by another annotator.

Finally, specific patterns in
date and time are identified by the timestamp annotator in which the
occurrence of date and time annotation contiguously is found in
text.

The various
time stamp formats are-

Binary format -
101100100000010111100001111100011011

Octal format - 13102714657

Hexadecimal formats - 590b99af

Hexatrigesimal format - opg5nz

Barcode format - the ones we see
on the labels behind different products.

Human readable date can be
converted to timestamps of various formats too.

The various types of timestamp
formats can also be converted to human readable date.

The year 2038 problem

We know that the UNIX epoch is
January 1st 1970, which is the point from which the Unix
time stamps are calculated. However, on January 19th 2038,
the Unix time stamp will stop working. This will happen due to a 32
bit overflow. Hence, before this moment two things can happen-

One, millions and millions of
application will either need to find and adopt a new convention for
time stamps.

Two, they migrate to 64 bit
systems. This can buy a little more time before the time stamp
expires.

Limitations of time stamps

One major limitation as we
already discussed is the 2038 problem.

Secondly, timestamp values are
stored as epoch seconds and hence the timestamp value of
1970-01-01-00:00:00 is reserved as the second #0 and represents
0000-00-00 00:00:00

Thirdly, previously there could
be only one timestamp column per table in Maria DB 5.5 and this had
the current timestamp defined as default value. This limit, however,
does not apply anymore to MariaDB 10.0.

Disadvantages
and advantages of Time stamps in SQL servers-

Timestamp as Datetime - The data
type of a timestamp is merely an incrementing number and it actually
doesn't preserve the date or time. A datetime data type is to be
used in order to record a date and a time.

Timestamp as data base
timestamp- there is a counter for each database that increments for
each update operation or insert, which is performed on a table
containing a timestamp column in the database. This is the database
timestamp. This does not track the actual time but the relative
time.

Timestamp as primary key- a
timestamp column should not be considered as a primary key owing to
the fact that the value is changing. Whenever an update is made to
the row, the timestamp value changes.

Timestamp can be used as value
changes identification.

Timestamps can be duplicated.
However, Microsoft would definitely not recommend using the
timestamp that way.

There are much more that we can talk
about. The basic idea is that timestamp can be really useful but
there are situations when it would be best to consider date and time
instead of timestamp.