How is the timestamp calculated?

Question

The timestamp is the numerical representation of a date and is widely used in programming languages and in databases. I want to know:

How is this calculation made?

Is there any standardization for this calculation an ISO or something like?

I’ve heard of unix timestamp. There are other flavors of timestamp?

Answer 1

What is the Unix timestamp?

How is this calculation made?

Is there any standardization for this calculation an ISO or something like?

The Unix timestamp corresponds to the number of seconds since midnight on 01/01/1970 in UTC without taking into account the second leap (as mentioned in ctgPi response). To simplify, let’s call this moment zero point time. Thus, the Unix timestamp is the number of seconds from zero.

The Unix timestamp is usually represented in various ways:

• When represented as a 32-bit integer with signal, the Unix timestamp can represent dates between 13/12/1901 20:45:52 until 19/01/2038 03:14:07, both in UTC. Mysql for example, uses the type TIMESTAMP stored like this, and is therefore susceptible to bug of the year 2038, what is more-or-less a Unix version of the infamous bug of the year 2000.
• When represented as an unsigned 32-bit integer, it can represent dates between 01/01/1970 00:00:00 until 07/02/2106 06:28:15, again both in UTC.
• When represented by a 64-bit signal number, it can represent dates between 01/27/292277022657 08:29:52 until 12/04/292277026596 15:30:07 (disregarding the fact that before 1582 the applied calendar was not the Gregorian). That’s a long enough interval to include the entire period of time from long before the Big Bang to a point in such a distant future that the universe will already be very different from how we know it.

There are several ways to convert from Unix timestamp for a date and vice versa. Some forms are simpler, others more complicated. But what matters is that they all arrive at the same result in all cases. So, here’s the algorithm to get a date/time from the Unix timestamp:

Algorithm for converting Unix timestamp for date/time:

1. If we catch the Unix timestamp and divide by 60, the division quotient (let’s call it minutosUnix) will be the number of minutes from zero point. The rest will be the amount of seconds passed in the minute. For negative values of the Unix timestamp, where the quotient is zero or negative and the rest is negative (but not zero), one must add 60 to the rest obtained and subtract 1 from the quotient obtained.

2. If we catch the minutosUnix and divide by 60, the division quotient (let’s call it horasUnix) will be the number of hours from zero point. The rest will be the amount of minutes spent in the hour. For negative values of minutosUnix, where the quotient is zero or negative and the rest is negative (but not zero), one must add 60 to the rest obtained and subtract 1 from the quotient obtained.

3. If we catch the horasUnix and divide by 24, the division quotient (let’s call it diasUnix) will be the number of days from zero point. The rest will be the amount of hours spent on the day. For negative values of horasUnix, where the quotient is zero or negative and the rest is negative (but not zero), one must add 24 to the rest obtained and subtract 1 from the quotient obtained.

From here on, the situation gets a little more complicated. The Gregorian calendar is repeated every 400 years, since we have leap years every 4 years (the years whose number is divisible by 4) but with three exceptions, namely, the years divisible by 100 but not by 400.

Thus, each 400-year cycle in the Gregorian calendar has 146097 days, as:

    ((365 * 4) + 1) * 100 - 3 = 146097

4. Let’s call it like ciclosDe400Anos the ratio of the diasUnix by 146097 and as diasEm400Anos the rest. For negative values of diasUnix, where the quotient is zero or negative and the rest is negative (but not zero), one must add 146097 to the rest obtained and subtract 1 from the quotient obtained.

In this 400-year cycle, with the three exceptions cited, we have four-year cycles with three non-leap years and one leap year, totaling 1461 days. That means that in these 400 years there are 97 cycles of 4 years with 1461 days and 3 cycles of 4 years with 1460 days. Whereas the 4-year cycle of number 0 begins on 01/01/1970 and ends on 12/31/1973, then the 4-year cycles which has 1460 days within these 400 years are the number 32 cycles - which corresponds to the cycle of 2098-2101 containing the year 2100, the number 57 cycle - which corresponds to cycle 2198-2201 containing year 2200 and cycle number 82 - which corresponds to cycle 2298-2301 containing year 2300. These three cycles are without the day 29 of February, but we can add artificially the days 29 of February missing by adding one day from 01/03/2100, another day from 01/03/2200 and another from 01/03/2300, making all cycles have 1461 days. Thus, February 29 skipped (which is actually March 1), occurs after 789 days are spent in each of these 4-year cycles (two years of 365 days plus January 31 days plus February 28 days = 789 days). In this way:

5. We increase diasEm400Anos if the value is greater than or equal to (32 * 1461 + 789), increase again if it is greater than or equal to (57 * 1461 + 789) and increment a third time if it is greater than or equal to (82 * 1461 + 789). This will account for the three days February 29 skipped, and will make the resulting calendar repeat every four years of 1461 days and the three days artificially introduced will never occur.

6. Let’s call it like ciclosDeQuatroAnos the ratio of the diasEmQuatroAnos by 1461 and as diasEmQuatroAnos the rest.

To reduce the period to one year instead of four, we can do something similar to what was done in step 5 to consider the three days February 29 skipped in the four years. Considering that in the four-year cycle started in 1970 (the same will apply to all the others), the first February 29 skipped (in 1970) would be after 59 days passed (31 in January and 28 in February), the 1971 would be after 424 days and the 1973 after 1155 days, then:

7. We increase diasEmQuatroAnos if the value is greater than or equal to 59, we increment again if it is greater than or equal to 425 and increment a third time if it is greater than or equal to 1157. This will account for the three days February 29 skipped, and will make the resulting calendar repeat each year of 366 days. The number 425 is used instead of 424 because of the day added after the 59. The number 1157 is used instead of 1155 because of the two days added on 59 and 425.

Now, we will have years of 366 days, already skipped every day February 29 that should be skipped and we will have a cycle of 4 years with 1464 days (including the three days added artificially that will never occur). So we can finally get the year:

8. Let’s call it like anoEmQuatroAnos the ratio of the diasEmQuatroAnos by 366 and as diasNoAno the rest.

9. The year shall be the sum of anoEmQuatroAnos, plus four times the value of ciclosDeQuatroAnos, four hundred times the value of ciclosDe400Anos more 1970.

And finally, we get the day and the month:

10. Build a table with the number of days in each month, which corresponds to [31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31] and the day of which month is the value of diasNoAno, subtracting from diasNoAno the days of each month until it is no longer possible to subtract. The month in which it is not possible to subtract is the month corresponding to the Unix timestamp data. The number of remaining days plus one shall be the day corresponding to the Unix timestamp datum.

The algorithm outlined above only works for dates of the gregorian calendar. This means that it will produce incorrect results for Unix timestamps corresponding to dates prior to adoption of this calendar.

After implementing this, if you want, you can make the time zone conversion to one that is not UTC. Again, it is worth noting that the leap seconds are disregarded.

Date/time conversion algorithm to Unix timestamp:

To do the reverse procedure:

1. Subtract 1970 from year.

2. We take the year minus 1970 and divide by 400, calling the division quotient periodosDe400Anos and the rest of anoNoPeriodoDe400Anos. For values where the year subtracted from 1970 is negative and the quotient is zero or negative and the rest is negative (but not zero), one must add 400 to the rest obtained and subtract 1 from the quotient obtained.

3. We got the anoNoPeriodoDe400Anos and divide by 4, denominating the quotient of the division of periodosDe4AnosNos400 and the rest of anoNoPeriodoDe4Anos.

4. We calculated the number of days in the previous years in the four-year period (and we call this diasNosAnosAnterioresDoPeriodoDe4Anos) when looking at the value of anoNoPeriodoDe4Anos. It will be 365 days for each previous year. If the value of anoNoPeriodoDe4Anos is 3 (that is, the last of the 4-year period), then the immediately preceding year was leap (disregarding for the time being the cases of the years 2100, 2200 and 2300), and because of this, one more day must be added in this case.

5. We calculated the number of days spent in the year by taking the number of days, subtracting 1 and adding the number of days in all previous months in the year to the table of months. The table is [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]. Note that I am considering February as having 28 days. This will be corrected later if necessary.

6. The number of days since 01/01/1970 (still needing later correction because of leap years) is calculated. This is computed as the sum of the number of days in the year, diasNosAnosAnterioresDoPeriodoDe4Anos, periodosDe4AnosNos400 times 1461 and periodosDe400Anos times 146097.

7. The correction of leap years is applied. If anoNoPeriodoDe4Anos is equal to 2 and the month is March forward, add one day. If anoNoPeriodoDe400Anos is greater than 130, or equal to 130 in March or later, one day is added. The same is true for years 230 and 330. This gives the total number of days since 01/01/1970.

8. The Unix timestamp resulting is the number of seconds, plus the number of minutes times 60, plus the number of hours times 3600 (1 hour = 3600 seconds), plus the number of days since 01/01/1970 times 86400 (1 day = 86400 seconds).

Again, the algorithm outlined above only works for Gregorian calendar dates.

Algorithm to obtain the day of the week from the Unix timestamp:

To get yourself the day of the week from the Unix timestamp, the procedure is also simple:

1. Divide the Unix timestamp by 86400 to get the number of days since 01/01/1970 instead of the number of seconds. For negative values of the Unix timestamp, where the quotient is zero or negative and the rest is negative (but not zero), one must subtract 1 from the quotient obtained.

2. Add 4 days and get the rest of the division by 7. For negative values of number of days (after adding 4), where the quotient is zero or negative and the rest is negative (but not zero), one must add 7 to the rest obtained in order to make it positive.

3. Map the resulting number so that 0 is Sunday, 1 is Monday, etc.

Implementation:

Here’s a style implementation reinvent the wheel in Java, especially in the constructor that takes the timestampUnix. The method getTimestamp() does the reverse process. The method getDiaDaSemana() gets the day of the week. Translating the code below into other programming languages should be relatively easy too:

import java.util.Objects;

public final class DataUTC implements Comparable<DataUTC> {
    private final int segundo;
    private final int minuto;
    private final int hora;
    private final int dia;
    private final int mes;
    private final long ano;

    private static int restoSemSinal(long a, int b) {
        return (int) (a >= 0L
                ? a % b // Positivo.
                : (b + (a % b)) % b); // Negativo.
    }

    private static long divisaoSemSinal(long a, int b) {
        return a >= 0L
                ? a / b // Positivo.
                : (a / b) - (a % b == 0 ? 0 : 1); // Negativo.
    }

    public DataUTC(int segundo, int minuto, int hora, int dia, int mes, long ano) {
        this.segundo = segundo;
        this.minuto = minuto;
        this.hora = hora;
        this.dia = dia;
        this.mes = mes;
        this.ano = ano;
    }

    public DataUTC(long timestampUnix) {
        // Passo 1.
        long minutosUnix = divisaoSemSinal(timestampUnix, 60);
        segundo = restoSemSinal(timestampUnix, 60);

        // Passo 2.
        long horasUnix = divisaoSemSinal(minutosUnix, 60);
        minuto = restoSemSinal(minutosUnix, 60);

        // Passo 3.
        long diasUnix = divisaoSemSinal(horasUnix, 24);
        hora = restoSemSinal(horasUnix, 24);

        // Passo 4.
        long ciclosDe400Anos = divisaoSemSinal(diasUnix, 146097);
        int diasEm400Anos = restoSemSinal(diasUnix, 146097);

        // Passo 5.
        if (diasEm400Anos >= 32 * 1461 + 789) diasEm400Anos++;
        if (diasEm400Anos >= 57 * 1461 + 789) diasEm400Anos++;
        if (diasEm400Anos >= 82 * 1461 + 789) diasEm400Anos++;

        // Passo 6.
        int ciclosDe4Anos = diasEm400Anos / 1461;
        int diasEm4Anos = diasEm400Anos % 1461;

        // Passo 7.
        if (diasEm4Anos >= 59) diasEm4Anos++;
        if (diasEm4Anos >= 425) diasEm4Anos++;
        if (diasEm4Anos >= 1157) diasEm4Anos++;

        // Passo 8.
        int anoEm4Anos = diasEm4Anos / 366;
        int diasNoAno = diasEm4Anos % 366;

        // Passo 9.
        ano = anoEm4Anos + ciclosDe4Anos * 4 + ciclosDe400Anos * 400 + 1970;

        // Passo 10.
        int[] tabelaDeMeses = {31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31};
        int contagemDeMeses = 0;
        while (diasNoAno >= tabelaDeMeses[contagemDeMeses]) {
            diasNoAno -= tabelaDeMeses[contagemDeMeses];
            contagemDeMeses++;
        }
        mes = contagemDeMeses + 1;
        dia = diasNoAno + 1;
    }

    public long getTimestamp() {
        // Passo 1.
        long anosDesde1970 = ano - 1970;

        // Passo 2.
        long periodosDe400Anos = divisaoSemSinal(anosDesde1970, 400);
        int anoNoPeriodoDe400Anos = restoSemSinal(anosDesde1970, 400);

        // Passo 3.
        int periodosDe4AnosNos400 = anoNoPeriodoDe400Anos / 4;
        int anoNoPeriodoDe4Anos = anoNoPeriodoDe400Anos % 4;

        // Passo 4.
        int diasNosAnosAnterioresDoPeriodoDe4Anos = 365 * anoNoPeriodoDe4Anos + (anoNoPeriodoDe4Anos == 3 ? 1 : 0);

        // Passo 5.
        long diasNoAno = dia - 1;
        int[] tabelaDeMeses = {31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31};
        for (int i = 0; i < mes - 1; i++) {
            diasNoAno += tabelaDeMeses[i];
        }

        // Passo 6.
        long dias = diasNoAno
                + diasNosAnosAnterioresDoPeriodoDe4Anos
                + periodosDe4AnosNos400 * 1461
                + periodosDe400Anos * 146097;

        // Passo 7.
        if (anoNoPeriodoDe4Anos == 2 && mes > 2) dias++;
        if (anoNoPeriodoDe400Anos > 130 || (anoNoPeriodoDe400Anos == 130 && mes > 2)) dias--;
        if (anoNoPeriodoDe400Anos > 230 || (anoNoPeriodoDe400Anos == 230 && mes > 2)) dias--;
        if (anoNoPeriodoDe400Anos > 330 || (anoNoPeriodoDe400Anos == 330 && mes > 2)) dias--;

        // Passo 8.
        return segundo + 60 * minuto + 60 * 60 * hora + 60 * 60 * 24 * dias;
    }

    public static enum DiaDaSemana {
        DOMINGO, SEGUNDA_FEIRA, TERCA_FEIRA, QUARTA_FEIRA, QUINTA_FEIRA, SEXTA_FEIRA, SABADO;
    }

    public DiaDaSemana getDiaDaSemana() {
        // Passo 1.
        long diasDesde1970 = divisaoSemSinal(getTimestamp(), 86400);

        // Passo 2.
        int diaDaSemana = restoSemSinal(diasDesde1970 + 4, 7);

        // Passo 3.
        return DiaDaSemana.values()[diaDaSemana];
    }

    public int getSegundo() {
        return segundo;
    }

    public int getMinuto() {
        return minuto;
    }

    public int getHora() {
        return hora;
    }

    public int getDia() {
        return dia;
    }

    public int getMes() {
        return mes;
    }

    public long getAno() {
        return ano;
    }

    @Override
    public int hashCode() {
        return Objects.hash(segundo, minuto, hora, dia, mes, ano);
    }

    @Override
    public boolean equals(Object obj) {
        if (!(obj instanceof DataUTC)) return false;
        DataUTC outro = (DataUTC) obj;
        return this.segundo == outro.segundo && this.minuto == outro.minuto && this.hora == outro.hora
                && this.dia == outro.dia && this.mes == outro.mes && this.ano == outro.ano;
    }

    @Override
    public int compareTo(DataUTC other) {
        Objects.requireNonNull(other);
        if (this.ano != other.ano) return this.ano > other.ano ? 1 : -1;
        if (this.mes != other.mes) return this.mes > other.mes ? 1 : -1;
        if (this.dia != other.dia) return this.dia > other.dia ? 1 : -1;
        if (this.hora != other.hora) return this.hora > other.hora ? 1 : -1;
        if (this.minuto != other.minuto) return this.minuto > other.minuto ? 1 : -1;
        if (this.segundo != other.segundo) return this.segundo > other.segundo ? 1 : -1;
        return 0;
    }

    @Override
    public String toString() {
        return String.format("%02d/%02d/%04d %02d:%02d:%02d", dia, mes, ano, hora, minuto, segundo);
    }
}

And here’s the test of the class above:

import static DataUTC.DiaDaSemana.*;

public class Main {

    public static void main(String[] args) {
        test(-12_005_355_554L, "26/07/1589 03:00:46", QUARTA_FEIRA);
        test( -1_234_567_890L, "18/11/1930 00:28:30", TERCA_FEIRA);
        test(   -777_777_777L, "09/05/1945 22:37:03", QUARTA_FEIRA);
        test(              -1, "31/12/1969 23:59:59", QUARTA_FEIRA);
        test(              0L, "01/01/1970 00:00:00", QUINTA_FEIRA);
        test(              1L, "01/01/1970 00:00:01", QUINTA_FEIRA);
        test(      451551900L, "23/04/1984 07:05:00", SEGUNDA_FEIRA);
        test(    915_148_801L, "01/01/1999 00:00:01", SEXTA_FEIRA);
        test(  1_000_000_000L, "09/09/2001 01:46:40", DOMINGO);
        test(  1_234_567_890L, "13/02/2009 23:31:30", SEXTA_FEIRA);
        test(  4_294_967_295L, "07/02/2106 06:28:15", DOMINGO);
        test(  6_505_355_555L, "23/02/2176 11:12:35", SEXTA_FEIRA);
        test( 15_505_445_554L, "07/05/2461 04:12:34", SABADO);
    }

    public static void test(long timestamp, String esperado, DataUTC.DiaDaSemana diaEsperado) {
        DataUTC data = new DataUTC(timestamp);
        String dataObtida = data.toString();
        long timestampObtido = data.getTimestamp();
        DataUTC.DiaDaSemana diaObtido = data.getDiaDaSemana();
        if (!esperado.equals(dataObtida) || timestamp != data.getTimestamp() || diaEsperado != diaObtido) {
            System.out.println("Falhou para " + timestamp + " -> " + esperado + " " + diaEsperado
                    + ", veio " + dataObtida + " (" + timestampObtido + ") " + diaObtido);
        } else {
            System.out.println("Funcionou para " + timestamp + " -> " + esperado + " " + diaEsperado + ".");
        }
    }
}

And here’s the test result:

Funcionou para -12005355554 -> 26/07/1589 03:00:46 QUARTA_FEIRA.
Funcionou para -1234567890 -> 18/11/1930 00:28:30 TERCA_FEIRA.
Funcionou para -777777777 -> 09/05/1945 22:37:03 QUARTA_FEIRA.
Funcionou para -1 -> 31/12/1969 23:59:59 QUARTA_FEIRA.
Funcionou para 0 -> 01/01/1970 00:00:00 QUINTA_FEIRA.
Funcionou para 1 -> 01/01/1970 00:00:01 QUINTA_FEIRA.
Funcionou para 451551900 -> 23/04/1984 07:05:00 SEGUNDA_FEIRA.
Funcionou para 915148801 -> 01/01/1999 00:00:01 SEXTA_FEIRA.
Funcionou para 1000000000 -> 09/09/2001 01:46:40 DOMINGO.
Funcionou para 1234567890 -> 13/02/2009 23:31:30 SEXTA_FEIRA.
Funcionou para 4294967295 -> 07/02/2106 06:28:15 DOMINGO.
Funcionou para 6505355555 -> 23/02/2176 11:12:35 SEXTA_FEIRA.
Funcionou para 15505445554 -> 07/05/2461 04:12:34 SABADO.

Representations in the database and programming languages

Databases represent dates in several different ways, not necessarily with the Unix timestamp.

• As mentioned above, the TIMESTAMP mysql stores a Unix timestamp 32-bit signal, so it’s susceptible to the year 2038 bug. The format DATETIME Mysql fixes the format problem TIMESTAMP of the same, can store from 01/01/1000 until 31/12/9999. None of these formats store fractions of a second, and therefore to store them, you will have to put them in some numerical column for such purpose.

• In Mariadb (which for those who do not know, is a Mysql Fork), the types TIMESTAMP and DATETIME are similar to Mysql (including the year 2038 bug for TIMESTAMP). However, unlike Mysql, Mariadb accepts fractions of a second with maximum microsecond accuracy.

• In the Oracle, the guy TIMESTAMP is able to store dates from 4712 BC to 9999, with an accuracy that can be defined from 1 second to 1 nanosecond (billionths of a second). including (or not) time zone information. The type TIMESTAMP WITH TIME ZONE also stores the time zone.

• No Postgresql, the guy TIMESTAMP (or TIMESTAMP WITHOUT TIME ZONE), with 8 bytes, is able to store dates of 4713 before Christ until the year 294276, with precision of microseconds (millionths of a second). It is also possible to reduce the accuracy to increase the accepted range. There is also the type TIMESTAMPTZ (or TIMESTAMP WITH TIME ZONE) which also stores the time zone.

• In SQL Server, the guy TIMESTAMP is an equivalence deprecated for the guy ROWVERSION, that actually constitutes a field that is automatically changed whenever a record is changed and stores a guaranteed unique value, unrelated to dates. In SQL Server, the type DATETIME uses 8 bytes to store dates from 01/01/1753 to 12/31/9999, with accuracy of about 3 milliseconds. There’s also the guy SMALLDATETIME using 4 bytes to store dates from 01/01/1900 to 06/06/2079 with 1 minute accuracy, the guy DATETIME2, which stores any representative date and time with a four-digit year with an accuracy of tenths of millionths of a second. and a guy DATETIMEOFFSET which also stores the time zone.

• No Firebird, the guy TIMESTAMP uses 4 bytes to store the date and 4 bytes to store the time, totaling 8 bytes. The date can vary from 01/01/0001 to 31/12/9999 and the time has precision of tenths of thousandths of seconds.

• Most modern programming languages use Unix timestamp. In C, which is either used as a basis for other languages or imitated by them, the type time_t is not set with any fixed size or format. In older systems, it was represented as a 32-bit integer with signal, and was therefore susceptible to the year 2038 bug. In modern systems, it is often implemented as a 64-bit integer with signal. (link)

Other formats:

There are other flavors of timestamp?

• Excel (such as the ctgPi mentioned in his reply) uses as a basis the number of days since 01/01/1900, taking the hours as fractional part. Interestingly, this format comes from the Lotus 1-2-3 Paleozoic, which had a bug that made the year 1900 be considered leap and this bug exists in Excel until today (source). Correcting the bug, its base date becomes 31/12/1899. The implementation of this format, would be similar to the implementation given above for the Unix timestamp, using as a basis the year 1900 instead of 1970, working with floating point numbers instead of integers, multiplying/dividing the timestamp by 86400 and taking care of the day (which did not exist) February 29, 1900.

• There is a variant of Unix timestamp which consists of counting milliseconds since 01/01/1970 instead of the number of seconds. Simply multiply/divide the timestamp per 1000 and add the milliseconds.

• Another notable example is the old MS-DOS date/time format, which was also used by the ZIP format. This format consisted of the date storage in 2 bytes and the time in another 2 bytes:

  • For the date, the first 7 bits represented the year since 1980 (and therefore could only represent years from 1980 until 2107), the next 4 bits represented the month and the last 5 bits represented the day.
  • The time, was stored with 5 bits for the hour, 6 bits for the minute and 5 bits for the second divided by two. This detail of the seconds, made its accuracy/resolution of 2 seconds, being the resulting number of seconds always even, because it was stored as a number from 0 to 29 to be multiplied by 2, which caused several problems in programs that expected the number of the second not to have this behavior.

And the ISO?

There’s a ISO 8601 format date representation (actually a set of formats). This has nothing to do with the Unix timestamp, is only a universal way of representing dates, since different countries represent it differently, which can lead to confusion. For example, in Brazil it is customary to use DD/MM/YYYY, while in the US it is used MM/DD/YYYY, in Armenia is used DD.MM.YYYY, in Japan is used YYYY年MM月DD日. (source)

The ISO 8601 format consists of one of the following:

• Year in format YYYY.
• Year and month in format YYYY-MM.
• Year, month and day in format YYYY-MM-DD.
• Year, month, day, hours, minutes and time zone in format YYYY-MM-DDThh:mmTZD.
• Year, month, day, hours, minutes, seconds and time zone in format YYYY-MM-DDThh:mm:ssTZD.
• Year, month, day, hours, minutes, seconds, fractions of seconds and time zone in format YYYY-MM-DDThh:mm:ss.sTZD.

Where:

• YYYY is the year with four digits.
• MM is the month with two digits.
• DD is the double digit day.
• hh is the double digit hour.
• mm are the two-digit minutes.
• ss are the two-digit seconds.
• s are fractions of seconds, with one or more digits.
• TZD is the time zone.
• The characters -, :, T and . are written literally.

The time zone can have the format +hh:mm or -hh:mm to specify a displacement relative to UTC, or can be represented as Z to express UTC itself.

So these are some dates and times expressed in ISO 8601:

• 2012
• 2012-04
• 2012-04-21
• 2012-04-21T12:21Z
• 2012-04-21T12:21+03:00
• 2012-04-21T12:21:44-10:30
• 2012-04-21T12:21:44.6-04:00
• 2012-04-21T12:21:44.67893-09:20
• 2012-04-21T12:21:44.67893Z

---

More details about the Gregorian calendar provided by PPK (thanks to ctgPi for the comment). Here is the link: http://www.quirksmode.org/blog/archives/2009/04/making_time_saf.html

Answer 2

As Earendul observed, the timestamp of UNIX are the seconds elapsed since midnight on January 1, 1970 UTC. For example, on my machine,

landingpad:~ fmoreira$ date -r 0
Wed Dec 31 21:00:00 BRT 1969

Note that if you are doing time accounts for physics or astronomy experiments, UNIX ignores leap seconds.

Excel, on the other hand, measures dates and times in a whole number of days since January 1 1900 (hours, minutes and seconds are represented as day fractions); you can check this by formatting a column of common numbers as date.

How the Sgbds represent TIMESTAMP (in reality there are two types, the TIMESTAMP WITH TIME ZONE and the TIMESTAMP WITHOUT TIME ZONE) is an implementation detail, and therefore it is difficult to answer your question without knowing exactly which DBMS you have in mind (although most simply save a date/time with microsecond accuracy), but two important pranks are:

• The CURRENT_TIMESTAMP is not the current instant, but the instant at which the current transaction started (they are the same if you have AUTOCOMMIT on); this is useful if you want to use TIMESTAMPs as keys of any table - even in different commands, CURRENT_TIMESTAMP will always have the same value within the same transaction.

• In Mysql, the TIMESTAMP has a "Feature"where updates to the table row automatically update the timestamp of that row, even if you have not touched that specific column; en.SO has a solution to this problem.

• Also in Mysql, the TIMESTAMP suffers from the bug of the year 2038, as noted by Victor Stafusa; the DATETIME will only give trouble in the year 10000.