What is "timestamp"
A timestamp is a string, denoting the date and/or time at which a given event occurred. An hour is the time when an event is recorded by a computer, not the time of the event itself. In many cases, the difference may be irrelevant: the moment an event is recorded by a timestamp (for example, entered in a log file) must be very, very close to the time of occurrence of the recorded event.
In some cases, a timestamp may only be the numbering of events, the use of a *date_time* format to store a time tag is then not required.
This data is typically presented in a consistent format, allowing for easy comparison of two different records and monitoring of progress over time, the practice of recording time marks in a consistent manner along with the actual data are called timestamping.
Timestamp are usually used for log events, in which case each event in a log is marked with a timestamp. In file systems, timestamp can mean the stored date/time of creating or modifying a file.
There are 2 types of timestamp:
TIMESTAMP WITH TIME ZONE: is a variant of timestamp which includes a time zone shift (TIME_ZONE) in its value. We can say that the time zone shift is the difference in hours and minutes between the local time and the GMT.
Abstract: two TIMESTAMP WITH TIME ZONE values will be considered identical if they represent the same instant in GMT, regardless of time zone displacements (TIME ZONE) stored in the data:
For example:
timestamp '17/08/2009 08:00:00 -3:00?
is the same as
timestamp '17/08/2009 06:00:00 -5:00?
That is, 08:00 am Brasilia time is the same as 06:00 am New York time in the US.
TIMESTAMP WITH A LOCAL TIME ZONE: is another variant of timestamp which includes a time zone shift in its value. The difference is that this displacement is not stored as part of the data column, but normalized to the time zone of the database (DBTIMEZONE). The verb "normalize", in this case, means a calculation that is performed based on the time zone defined in the database in order to show the local time of the user:
Assuming I’m in Brazil (time zone -3:00) and enter the date and time below: timestamp '17/08/2009 08:00:00
and a user who is in New York (time zone -5:00) access the same data, the date presented to this user will be
timestamp '17/08/2009 06:00:00 To show the TIMESTAMP and its variations we can show as follows:
LOCALTIMESTAMP: returns a value of the type timestamp as current date and time (including fractions of seconds) according to the time zone defined in the user’s session.
CURRENT_TIMESTAMP: returns a value of type TIMESTAMP WITH TIME ZONE as current date and time (including fractions of seconds), according to the time zone set in the user’s session.
SYSTIMESTAMP: returns a value of type TIMESTAMP WITH TIME ZONE as current date and time (including fractions of seconds), according to the time zone defined in the system where the database server resides.
Examplos of timestamps:
- 2005-10-30 T 10:45 UTC
- 2007-11-09 T 11:20 UTC
- Sat Jul 23 02:16:57 2005
- 1256953732
Standardization
ISO 8601 standardizes the representation of dates and times. This standard representation are often used to construct values timestamp.