Current time in milliseconds online. UNIX time. Snippet Characterizing UNIX Time

What is Unix time or Unix epoch (Unix epoch or Unix time or POSIX time or Unix timestamp)?

UNIX-time or POSIX-time (English Unix time) - a way of encoding time, accepted in UNIX and other POSIX-compatible operating systems.
The beginning of the countdown is considered midnight (UTC) from December 31, 1969 to January 1, 1970, the time from that moment is called the "UNIX era" (English Unix Epoch).
UNIX time is consistent with UTC, in particular, when leap seconds are declared UTC, the corresponding second numbers are repeated.
The method of storing time in the form of a number of seconds is very convenient to use when comparing dates (with precision to the second), as well as for storing dates: if necessary, they can be converted to any readable format. Date and time in this format also take up very little space (4 or 8 bytes, depending on the size of the machine word), so it is reasonable to use it for storing large amounts of dates. Disadvantages in performance can appear when very frequent access to date elements, such as the number of the month, etc., can occur. But in most cases, it is more efficient to store time as a single value, rather than a set of fields.

Converting the Unix era to a human readable date

Unix start and end date of year, month or day

Converting seconds to days, hours and minutes

How to get Unix time in ...

Perl	time
PHP	time ()
Ruby	Time.now (or Time.new). To output: Time.now.to_i
Python	import time first, then time.time ()
Java	long epoch = System.currentTimeMillis () / 1000;
Microsoft .NET C #	epoch = (DateTime.Now.ToUniversalTime (). Ticks - 621355968000000000) / 10000000;
VBScript / ASP	DateDiff ("s", "01/01/1970 00:00:00", Now ())
Erlang	calendar: datetime_to_gregorian_seconds (calendar: now_to_universal_time (now ())) - 719528 * 24 * 3600.
MySQL	SELECT unix_timestamp (now ())
PostgreSQL	SELECT extract (epoch FROM now ());
SQL Server	SELECT DATEDIFF (s, "1970-01-01 00:00:00", GETUTCDATE ())
JavaScript	Math.round (new Date (). GetTime () / 1000.0) getTime () returns the time in milliseconds.
Unix / Linux	date +% s
Other OS	Command line: perl -e "print time" (If Perl is installed on your system)

Converting date to Unix time to ...

PHP	mktime ( watch, minutes, seconds, month, day, year)
Ruby	Time.local ( year, month, day, watch, minutes, seconds, usec) (or Time.gm for GMT / UTC output). To display add .to_i
Python	import time first, then int (time.mktime (time.strptime ("2000-01-01 12:34:00", "% Y-% m-% d% H:% M:% S")))
Java	long epoch = new java.text.SimpleDateFormat ("dd / MM / yyyy HH: mm: ss"). parse ("01/01/1970 01:00:00");
VBScript / ASP	DateDiff ("s", "01/01/1970 00:00:00", date field)
MySQL	SELECT unix_timestamp ( time) Time format: YYYY-MM-DD HH: MM: SS or YYMMDD or YYYYMMDD
PostgreSQL	SELECT extract (epoch FROM date ("2000-01-01 12:34")); With timestamp: SELECT EXTRACT (EPOCH FROM TIMESTAMP WITH TIME ZONE "2001-02-16 20: 38: 40-08"); With an interval: SELECT EXTRACT (EPOCH FROM INTERVAL "5 days 3 hours");
SQL Server	SELECT DATEDIFF (s, "1970-01-01 00:00:00", date field)
Unix / Linux	date +% s -d "Jan 1, 1980 00:00:01"

Converting Unix Time to Human Readable Date ...

PHP	date ( Format, unix time);
Ruby	Time.at ( unix time)
Python	import time first, then time.strftime ("% a,% d% b% Y% H:% M:% S +0000", time.localtime ( unix time)) Replace time.localtime with time.gmtime for GMT date.
Java	String date = new java.text.SimpleDateFormat ("dd / MM / yyyy HH: mm: ss"). Format (new java.util.Date ( unix time*1000));
VBScript / ASP	DateAdd ("s", unix time, "01/01/1970 00:00:00")
PostgreSQL	SELECT TIMESTAMP WITH TIME ZONE "epoch" + unix time* INTERVAL "1 second";
MySQL	from_unixtime ( unix time, optional, output format) Standard output format YYY-MM-DD HH: MM: SS
SQL Server	DATEADD (s, unix time, "1970-01-01 00:00:00")
Microsoft Excel	= (A1 / 86400) + 25569 The result will be in the GMT time zone. For other time zones: = ((A1 +/- time difference for the zone) / 86400) + 25569.
Linux	date -d @ 1190000000
Other OS	Command line: perl -e "print scalar (localtime ( unix time)) "(If Perl is installed) Replace" localtime "with" gmtime "for GMT / UTC time zone.

What is the "Unixtime Converter" tool for?

This tool, first of all, will be useful for webmasters who constantly deal with large amounts of dates or often refer to their elements in their work. Using the "Unixtime Converter" tool, you can easily convert Unix time into a user-friendly date (and vice versa), find out the current Unix epoch time, and get Unix time in various programming languages, DBMS and operating systems.

What is Unix Time?

The Unix era (Unix epoch) began on the night of December 31, 1969 to January 1, 1970. It was this date that was taken as the starting point for "computer" time, which is calculated in seconds and takes up very little disk space - only 4 or 8 bytes. Using this coding method, programmers can "hide" any date in one number, and easily convert it back into a format that users can understand.

Unix time (also called Unix time or POSIX time) is convenient to use in various operating systems and programming languages, since it is displayed as a single value, and not a certain number of fields that take up space. In addition, UNIX time fully complies with the UTC standard (including in leap years) - in this case, the corresponding seconds values are simply repeated.

Unix terminology

A few words about the terms.

So, Unix time(or POSIX time) is the number of seconds that have elapsed since midnight, January 1, 1970, to the present.

Unix Timestamp(timestamp) is a "fixed" time, in other words, a specific date stamped in a number.

UTC(Universal Coordinated Time) is the Coordinated Universal Time, which is "fixed" at the prime meridian, and from which geographic time zones are counted.

How "durable" is the system?

In just a couple of decades, namely on January 19, 2038 at 03:14:08 UTC, Unix time will reach the value 2147483648, and computer systems can interpret this number as negative. The key to solving this problem lies in using a 64-bit (instead of 32-bit) variable to store time. In this case, the stock of numerical values of Unix time will be enough for humanity for another 292 billion years. Not bad, right?

Unix time is one for all

If you live in London or San Francisco, and your friends are in Moscow, then you can "synchronize watches" using Unix time: this system is in this moment time is one for the whole world. Naturally, if the time on the servers is set correctly. And with the tool "Unixtime converter" this conversion will take you a split second.

UNIX time or POSIX time (English Unix time) - convenient way time coding, taken as the main one in UNIX and other POSIX-compatible operating systems.
The time itself is recorded in the elapsed seconds from the beginning of the Unix Epoch, which began at midnight (UTC) from December 31, 1969 to January 1, 1970. Unix time is consistent with UTC, in particular when declaring leap seconds UTC - the corresponding second numbers are repeated.

This storage method is supported by many systems and is very convenient, for example, dates are compared to the nearest second, and storage is compact and easily converted to any readable format. Of course, there are drawbacks, for example, when you often refer to date elements, such as the number of the month, etc. However, in most cases, efficiency is achieved by storing the date as a single value, instead of a set of fields.

Getting UnixTime in different environments:

Perl	time
PHP	time ()
Ruby	Time.now (or Time.new). To output: Time.now.to_i
Python	import time first, then time.time ()
Java	long epoch = System.currentTimeMillis () / 1000;
Microsoft .NET C #	epoch = (DateTime.Now.ToUniversalTime (). Ticks - 621355968000000000) / 10000000;
VBScript / ASP	DateDiff ("s", "01/01/1970 00:00:00", Now ())
Erlang	calendar: datetime_to_gregorian_seconds (calendar: now_to_universal_time (now ())) - 719528 * 24 * 3600.
MySQL	SELECT unix_timestamp (now ())
PostgreSQL	SELECT extract (epoch FROM now ());
SQL Server	SELECT DATEDIFF (s, "1970-01-01 00:00:00", GETUTCDATE ())
JavaScript	Math.round (new Date (). GetTime () / 1000.0) getTime () returns the time in milliseconds.
Unix / Linux	date +% s
Other OS	Command line: perl -e "print time" (If Perl is installed on your system)

How do I convert a regular date to UnixTime?

PHP	mktime ( watch, minutes, seconds, month, day, year)
Ruby	Time.local ( year, month, day, watch, minutes, seconds, usec) (or Time.gm for GMT / UTC output). To display add .to_i
Python	import time first, then int (time.mktime (time.strptime ("2000-01-01 12:34:00", "% Y-% m-% d% H:% M:% S")))
Java	long epoch = new java.text.SimpleDateFormat ("dd / MM / yyyy HH: mm: ss"). parse ("01/01/1970 01:00:00");
VBScript / ASP	DateDiff ("s", "01/01/1970 00:00:00", date field)
MySQL	SELECT unix_timestamp ( time) Time format: YYYY-MM-DD HH: MM: SS or YYMMDD or YYYYMMDD
PostgreSQL	SELECT extract (epoch FROM date ("2000-01-01 12:34")); With timestamp: SELECT EXTRACT (EPOCH FROM TIMESTAMP WITH TIME ZONE "2001-02-16 20: 38: 40-08"); With interval: SELECT EXTRACT (EPOCH FROM INTERVAL "5 days 3 hours");
SQL Server	SELECT DATEDIFF (s, "1970-01-01 00:00:00", date field)
Unix / Linux	date +% s -d "Jan 1, 1980 00:00:01"

Convert back, UnixTime seconds into a human-readable date:

PHP	date ( Format, unix time);
Ruby	Time.at ( unix time)
Python	import time first, then time.strftime ("% a,% d% b% Y% H:% M:% S +0000", time.localtime ( unix time)) Replace time.localtime with time.gmtime for GMT date.
Java	String date = new java.text.SimpleDateFormat ("dd / MM / yyyy HH: mm: ss"). Format (new java.util.Date ( unix time*1000));
VBScript / ASP	DateAdd ("s", unix time, "01/01/1970 00:00:00")
PostgreSQL	SELECT TIMESTAMP WITH TIME ZONE "epoch" + unix time* INTERVAL "1 second";
MySQL	from_unixtime ( unix time, optional, output format) Standard output format YYY-MM-DD HH: MM: SS
SQL Server	DATEADD (s, unix time, "1970-01-01 00:00:00")
Microsoft Excel	= (A1 / 86400) + 25569 Result will be in GMT time zone. For other time zones: = ((A1 +/- time difference for the zone) / 86400) + 25569.
Linux	date -d @ 1190000000
Other OS	Command line: perl -e "print scalar (localtime ( unix time)) "(If Perl is installed) Replace" localtime "with" gmtime "for GMT / UTC time zone.

Most of the information is stolen from: Unix Epoch).

Integer representation

Each day, a number representing Unix time is calculated as described in UTC (00: 00: 00Z), and increases by exactly 1 per second starting at midnight. Therefore, 16-09-2004 17: 55: 43.54, 64543.54 seconds from midnight, from the example above, will be represented in UNIX time by the number 1095292800 + 64543.54 = 1095357343.54. For dates before the origin, the number also increases, i.e. over time approaches zero.

The used integer system is convenient for comparing and storing dates (date and time in this format takes only 4 or 8 bytes). If you need to refer to date elements (day, month, year), seconds can be converted to any suitable format (and vice versa).

In programs for storing UNIX time, the signed integer type is used. Signed 32-bit numbers can refer to times from Friday, December 13, 1901, 20:45:52 to Tuesday, January 19, 2038, 03:14:07, inclusive.

To find out the current UNIX time on most UNIX-like systems, you can use the command date +% s.

"Jubilee" meanings

Time	Number of seconds
December 13, 1901, 20:45:52 UTC	Minimum date in signed 32-bit notation (0x80000000)
January 19, 2038 03:14:07 UTC	Maximum date in signed 32-bit notation (0x7FFFFFFF)
September 9, 2001 01:46:40 UTC	1,000,000,000 (1 giga second)
January 10, 2004, 13:37:04 UTC	1 073 741 824 = 2 30 (1 gibisecond)
March 18, 2005 01:58:31 UTC	1 111 111 111
September 6, 2008 12:12:05 UTC	1 220 703 125 = 5 13
February 13, 2009 at 23:31:30 UTC	1 234 567 890

The year 2038 problem

Apple iOS as a UNIX system problem

Apple's 64-bit iOS devices have a problem like UNIX systems. If we transfer the time on a device with a 64-bit processor to iOS control at 1:00 am on January 1, 1970 and reboot the device, being in the time zone from UTC +1: 30 or more, then after rebooting the device it will not turn on, the "white apple" will be on all the time. This happens due to the difference in time zones, that is: if you translate the time to 1:00 on January 1, 1970 in the UTC time zone +1: 30 or more, then the UNIX-time counter goes into minus, since the countdown is from UTC time that the system is unable to understand, as a result of which the counter freezes. The device doesn't even recover via DFU, but the problem has three solutions in other ways. The first way: just leave the device on and wait until the UNIX-time counter goes up (until it is proven that this way works). The second way: wait until the phone's battery is completely drained and the counter will reset itself to zero. The third way: disassemble the device and disconnect the battery for a while, then assemble the device, the counter will also be reset to zero and the device will work.

The problem is finally resolved in iOS 9.3.1 - now the device is available to set the date starting from January 1, 2001.

Software conversion

An example of the implementation in the C language of functions for converting between UNIX time and the usual representation in the form of date and time of day. The example is in standard 32-bit signed format. However, if you comment out the definition of _XT_SIGNED, the example will be assembled in an unsigned version.

Time conversion, header file

typedef unsigned short u16_t; typedef unsigned long u32_t; typedef signed short s16_t; typedef signed long s32_t; // DEF: standard signed format // UNDEF: non-standard unsigned format #define _XT_SIGNED #ifdef _XT_SIGNED typedef s32_t xtime_t; #else typedef u32_t xtime_t; #endif struct tm (/ * date and time components * / BYTE tm_sec; BYTE tm_min; BYTE tm_hour; BYTE tm_mday; BYTE tm_mon; u16_t tm_year;); void xttotm (struct tm * t, xtime_t secs); xtime_t xtmtot (struct tm * t);

Time conversion

#include "xtime.h" #define _TBIAS_DAYS ((70 * (u32_t) 365) + 17) #define _TBIAS_SECS (_TBIAS_DAYS * (xtime_t) 86400) #define _TBIAS_YEAR 1900 #define (MONTAB (year) ( 03) || ((year) == 0))? Mos: lmos) const s16_t lmos = (0, 31, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335); const s16_t mos = (0, 31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334); #define Daysto32 (year, mon) (((year - 1) / 4) + MONTAB (year)) ////////////////////////// ///////////////////////////////////////// xtime_t xtmtot (struct tm * t ) (/ * convert time structure to scalar time * / s32_t days; xtime_t secs; s32_t mon, year; / * Calculate number of days. * / mon = t-> tm_mon - 1; year = t-> tm_year - _TBIAS_YEAR; days = Daysto32 (year, mon) - 1; days + = 365 * year; days + = t-> tm_mday; days - = _TBIAS_DAYS; / * Calculate number of seconds. * / secs = 3600 * t-> tm_hour; secs + = 60 * t-> tm_min; secs + = t-> tm_sec; secs + = (days * (xtime_t) 86400); return (secs);) /////////////// //////////////////////////////////////////////// //// void xttotm (struct tm * t, xtime_t secsarg) (u32_t secs; s32_t days; s32_t mon; s32_t year; s32_t i; const s16_t * pm; #ifdef _XT_SIGNED if (secsarg> = 0) (secs = ( u32_t) secsarg; days = _TBIAS_DAYS;) else (secs = (u32_t) secsarg + _TBIAS_SECS; days = 0;) #else secs = secsarg; days = _TBIAS_DAYS; #endif / * days, hour, min, sec * / days + = secs / 86400; secs = secs% 86400; t-> tm_hour = secs / 3600; secs% = 3600; t-> tm_min = secs / 60; t-> tm_sec = secs% 60; / * determine year * / for (year = days / 365; days< (i = Daysto32(year, 0) + 365*year);) { --year; } days -= i; t->tm_year = year + _TBIAS_YEAR; / * determine month * / pm = MONTAB (year); for (mon = 12; days< pm[--mon];); t->tm_mon = mon + 1; t-> tm_mday = days - pm + 1; )

Convert time from Excel to Unix format

double et; // date and time in Excel format(also used in OpenOffice, Sierra Chart) time_t ut = round ((et * 86400) - 2209161600); // date and time in Unix format

Write a review on "UNIX Time"

Notes (edit)

External links

Snippet Characterizing UNIX Time

Chernyshev was sitting with a book of a French novel at the window of the first room. This room was probably formerly a hall; there was still an organ in it, on which some carpets were piled, and in one corner stood the folding bed of Bennigsen's adjutant. This adjutant was here. He, evidently tormented by a feast or business, sat on a rolled-up bed and dozed. Two doors led from the hall: one directly into the former living room, the other to the right into the study. From the first door, voices were heard speaking in German and occasionally in French. There, in the former drawing-room, at the request of the sovereign, were gathered not a council of war (the sovereign loved uncertainty), but some persons whose opinion about the impending difficulties he wished to know. It was not a council of war, but like a council of the elect to clarify certain issues for the sovereign personally. To this half-council were invited: Swedish General Armfeld, Adjutant General Wolzogen, Wintzingerode, whom Napoleon called a fugitive French subject, Michaud, Toll, not a military man at all - Count Stein and, finally, Pful himself, who, as Prince Andrew heard, was la cheville ouvriere [the basis] of the whole matter. Prince Andrey had the opportunity to examine him well, since Pful arrived shortly after him and walked into the drawing-room, stopping for a minute to talk to Chernyshev.
At first glance, Pfuel, in his Russian general's badly sewn uniform, which was sitting on it awkwardly, as if dressed, seemed to Prince Andrey familiar, although he had never seen it. It included Weyrother, Mack, and Schmidt, and many other German theoreticians of generals whom Prince Andrew managed to see in 1805; but he was more typical of all of them. Prince Andrew had never seen such a German theoretician, who united in himself everything that was in those Germans.
Pful was short, very thin, but broad-boned, of a rough, healthy build, with a wide pelvis and bony shoulder blades. His face was very wrinkled, with deeply inserted eyes. His hair in the front at the temples, obviously, was hastily smoothed out with a brush, and naively protruded from the back with tassels. He, restlessly and angrily looking around, entered the room, as if he was afraid of everything in the big room where he entered. Holding his sword with an awkward movement, he turned to Chernyshev, asking in German where the sovereign was. He evidently wanted to go through the rooms as soon as possible, finish the bows and greetings and sit down to work in front of the map, where he felt at home. He hastily nodded his head at the words of Chernyshev and smiled ironically, listening to his words that the sovereign was examining the fortifications that he, Pful himself, had laid according to his theory. He's something bassist and cool, as self-confident Germans say, grumbled to himself: Dummkopf ... or: zu Grunde die ganze Geschichte ... or: s "wird was gescheites d" raus werden ... [nonsense ... to hell with the whole thing ... (German) ] Prince Andrey did not hear and wanted to pass, but Chernyshev introduced Prince Andrey to Pful, noting that Prince Andrey had come from Turkey, where the war had ended so happily. Pful slightly glanced not so much at Prince Andrew as through him, and said laughing: "Da muss ein schoner taktischcr Krieg gewesen sein." ["That must have been the right tactical war." (German)] - And, laughing contemptuously, went into the room, from which voices were heard.
Apparently, Pful, already always ready for ironic irritation, was especially excited today that they dared to inspect his camp without him and judge him. From this short meeting with Pful, Prince Andrew, thanks to his Austerlitz memoirs, compiled a clear description of this man. Pful was one of those hopelessly, unchanging, before the martyrdom of self-confident people that only Germans are, and precisely because only Germans are self-confident on the basis of an abstract idea - science, that is, an imaginary knowledge of perfect truth. The Frenchman is self-confident because he reveres himself personally, both in mind and in body, irresistibly charming for both men and women. The Englishman is self-confident on the grounds that he is a citizen of the most prosperous state in the world, and therefore, as an Englishman, he always knows what he needs to do, and knows that everything he does as an Englishman is undoubtedly good. The Italian is self-confident because he is agitated and easily forgets himself and others. The Russian is self-confident precisely because he does not know anything and does not want to know, because he does not believe that one could fully know anything. The German is self-confident worst of all, and hardest of all, and the most disgusting of all, because he imagines that he knows the truth, a science that he himself invented, but which for him is absolute truth. Such, obviously, was Pful. He had a science - the theory of the oblique movement, deduced by him from the history of the wars of Frederick the Great, and everything that he met in recent history wars of Frederick the Great, and everything that he met in modern military history seemed to him nonsense, barbarism, an ugly clash, in which so many mistakes were made on both sides that these wars could not be called wars: they did not fit the theory and did not could serve as a subject of science.
In 1806, Pful was one of the drafters of the plan for the war that ended with Jena and Auerstet; but in the outcome of this war he did not see the slightest proof of the incorrectness of his theory. On the contrary, the deviations from his theory, according to his ideas, were the only reason for all the failure, and he said with his characteristic joyful irony: “Ich sagte ja, daji die ganze Geschichte zum Teufel gehen wird”. [After all, I said that the whole thing would go to hell (German)] Pful was one of those theoreticians who love their theory so much that they forget the goal of theory - its application to practice; in love with theory, he hated all practice and did not want to know it. He even rejoiced at the failure, because the failure that resulted from deviating from theory in practice proved to him only the validity of his theory.
He said a few words with Prince Andrey and Chernyshev about a real war with the expression of a man who knows ahead of time that everything will be bad and that he is not even dissatisfied with it. The unkempt tassels of hair sticking out at the back of the head and hastily slicked down temples especially eloquently confirmed this.
He went into another room, and from there the bass and grumbling sounds of his voice were immediately heard.

Before Prince Andrey had time to see Pful with his eyes, Count Bennigsen hurriedly entered the room and, nodding his head to Bolkonsky, without stopping, walked into the office, giving some orders to his adjutant. The emperor followed him, and Bennigsen hurried forward to prepare something and have time to meet the emperor. Chernyshev and Prince Andrey went out onto the porch. The Emperor dismounted from his horse with a tired look. The Marquis Paulucci said something to the Emperor. The Emperor, bowing his head to the left, listened with an air of displeasure to Paulucci, who spoke with particular fervor. The Emperor moved forward, apparently wanting to end the conversation, but the flushed, agitated Italian, forgetting propriety, followed him, continuing to speak:
- Quant a celui qui a conseille ce camp, le camp de Drissa, [As for the one who advised the Drissa camp,] - Pauluchi said, while the sovereign, entering the steps and noticing Prince Andrew, peered into an unfamiliar face ...

Only for Lifeexample readers it is possible to open an online store on Moguta.CMS with a 15% discount

Unix Time and Unix Timestamp (MySQL, PHP, JavaScript)

Hello, dear blog readers, in this article, I want to tell you about what is Unix time and Unix Timestamp... Programmers often combine these concepts into one, but this is not entirely true. In addition, the article contains many useful notes on the topic of working with Unix Timestamp in PHP, MySQL and JavaScript.

Why Unix Time Starts On Jan 1, 1970

The thing is that Unix time begins counting the Unix era, with the release of the first UNIX system. The first system of this kind was created in 1969, so the developers took the date from January 1, 1970 at midnight UTC ( UTC).

Let's understand what Unix time and Unix Timestamp are for, and give them clear concepts.

Unix TimestampIs a timestamp that is a sequence of characters representing the number of seconds that have passed since January 1, 1970.

I'll try to give an example to clarify the difference between these two concepts.

At the time of this writing by me, Unix time it was equal 1346765877 .

At the time of reading, by you, this information, a record of the time ( 1346765877 ) is already a label - Unix Timestamp! By converting this timestamp into a readable form, we get the date 09/04/2012 and the time 17:37:57.

Frankly speaking, there is no special sense in separating the two concepts, in my opinion, but it is still useful to have an idea of what constitutes Unix Time and it is also useful to understand that the number of maximum possible seconds since 1970 has a limit!

The end of the Unix era will come in 2038

Fact: maximum binary number on 32 bit systems is the number 01111111 11111111 11111111 11111111 by translating it into decimal system, we get the number 2147483647.

January 19, 2038 at 03:14:08 the moment will come when the number of seconds that have passed since the beginning of the Unix era will exceed the maximum available in a 32-bit system, number = 2147483647. If the bit overflows, the date will be reset.

Test this theory on illustrative example very simple:

Open up standard calculator Windows press ALT + 3, this will convert it to engineering view;
Set 4 byte mode, and decimal input type;
Write the number 2147483647;

Note the binary representation of the number;
Add one to the number;

The addition will result in a negative number!

If we continue to add one, then we get a cyclic closure.

This kind of date ringing will occur from January 19, 2038 on all systems using 32-bit architecture.

In fact, there is no need to be sad, because the developers of computing systems are increasingly introducing 64-bit architectures into widespread use. Let's believe that they will be in time by 2038.

Now let's talk about using unix timestamp in php, mysql and even in javascript.

Working with unix timestamp

Highly important point, when working with unix timestamp in php or mysql, it is necessary to clearly understand the pros and cons of this date format.

For example, TIMESTAMP cannot be used to specify historical events or events of the distant future. The entire set of dates is limited to the period from 1970 to early 2038. If you set a date beyond 2038, it will not be correctly interpreted by the 32 bit system.

Realizing this limitation, a logical question arises: " Why bother with representing the date in seconds?"

When to use Unix Timestamp

To represent time in our usual system of measuring it, 8 bytes are required, and for unix timestamp it is half as much - 4 bytes.

Saving the amount of data, in my opinion, is the main and indisputable plus in using Unix Time.

In addition, there are a number of useful nuances available when working with UNIX timestamp in mysql... And since all information must be stored on the database server, and it, in turn, has a number of advantages when working with Unix timestamps, the choice towards unix timestamp can be correctly justified by the following provisions.

MySQL provides the appropriate Timestamp data type for working with the unix-time format, setting which we immediately get a useful advantage over the standard formats DATE and DATETIME... The advantage is that by performing the add operation new entry into a table, the column with this data type is filled in automatically. This means that we can save not only on the amount of data, but also on the processor time of the web server.

To reinforce the word with deeds, we set the following task: when registering a new user in the system, you need to enter the date of his addition to the database.

If the type of the field storing the date in the table is DATETIME, then the request from the PHP script will look something like this:

The benefits are obvious!

There is also a minus: if there are several TIMESTAMP fields, only the first is automatically updated.

Does it make sense to use INT instead of timestamp

Many programmers when working with unix timestamp use the integer format int (11). This is not a rational approach to the question at all, since MySQL provides many useful functions affecting the speed of working with it. Therefore, by storing the timestamp in INT, we deprive ourselves of all server support for this format. This is roughly the same as storing id in varchar (11) type.

However, there is one excuse for keeping unix timestamp to INT... When transferring a database between different DBMS, a type conflict may arise, i.e. for one of the DBMSs, the timestamp type may be unfamiliar. In this case, using int will take precedence since this format is in all DBMS.

Brief description of MySQL calendar types

TIMESTAMP- data type for storing date and time. The data is stored as the number of seconds since the beginning of the "Unix era". The range of values is 1970-01-01 00:00:00 - 2038-12-31 00:00:00. It takes 4 bytes.

DATE- the data type for storing the date. The range of values is 1000-01-01 through 9999-12-31. It takes 3 bytes.

DATETIME- data type for storing date and time. The range of values is 1000-01-01 00:00:00 - 9999-12-31 00:00:00. It takes 8 bytes and is stored as a number YYYYMMDDHHMMSS./p>

YEAR- data type for storing the year. Values range: 1901 - 2155. Occupies 1 byte.

TIME- data type for storing time. The range is −828: 59: 59 - 828: 59: 59. It takes 3 bytes.

Date translation in unix

It's time to lay out some useful functions for translating the date into unix timestamp and back from unix-time on a readable date.

How to get the current UNIX time

PHP:

time ();
JavaScript:

Math.round (new Date () .getTime () / 1000.0);
MySQL:

SELECT unix_timestamp (now ());