Using MySQL's Timestamp VS Storing Timestamps Directly

Using MySQL's TIMESTAMP vs storing timestamps directly

Arguments for TIMESTAMP

It implicitly stores data in UTC time zone. No matter what your session time-zone is. Useful if you need to use different time zones.
You can have automated timestamping columns using DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP (one column per table only until MySQL 5.6.5)
You can use datetime function for date comparison, addition, subtraction, range lookup etc, without the need to use FROM_UNIXTIME() function - it will make it easier to write queries that can use indexes
In PHP
```
>> date('Y-m-d h:i:s',4294967295);
'1969-12-31 11:59:59'
```
so the range is in fact the same
- You can still retrieve integer unix timestamp with no additional overhead using UNIX_TIMESTAMP() function: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp

When UNIX_TIMESTAMP() is used on a TIMESTAMP column, the function
returns the internal timestamp value directly, with no implicit
“string-to-Unix-timestamp” conversion

Should I use the datetime or timestamp data type in MySQL?

Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native DATETIME format. You can do calculations within MySQL that way
("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

Is storing a Unix timestamp a bad idea in the long term

There are 31,536,000 seconds in a year, so it will take over 269 years before another digit is added to unix timestamps.

(10,000,000,000 - 1,510,798,414) / 31,536,000 = 269.1908

So, on that count I'd say you're good for a while.

Depending on the database column type that you're using, a more immediate problem might be that from January 19, 2038 onward, you will no longer be able to store your timestamps in a signed 32 bit integer as they have a max value of 2,147,483,647.

So, I'd say: put a sticky note on your monitor to change your database column type on January 18, 2038. If you're using an unsigned 32 bit integer type, you're good until 2106.

Timestamps and time zone conversions in Java and MySQL

Date-Time Handling Is A Mess

The first paragraph in the answer by Teo is quite insightful and correct: Date-time handling in Java is a mess. Ditto for all other languages & development environments that I know of. Date-time work is difficult and tricky, especially error-prone and frustrating because we think it of date-time intuitively. But "intuitively" does not cut it when it comes to data types, databases, serialization, localization, adjusting across time zones, and all the other formalities that come with computer programming.

Unfortunately, the computer industry basically chose to ignore this problem of date-time work. Just as Unicode took too long to be invented given the obvious need, so too has the industry kicked the can down the road on solving the problem of date-time handling.

Do Not Rely On Count-Since-Epoch

But I must disagree with its conclusion. Working with a count-since-epoch is not the best solution. Using count-since-epoch is inherently confusing and error-prone and incompatible.

Humans cannot read a long number and decipher that as a date-time. So verifying data and debugging becomes complicated, to say the least.
What "count" would you use? The milliseconds used by java.util.Date and by Joda-Time? The microseconds used by Postgres, MySQL, and other databases? The nanoseconds used by the new java.time package in Java 8?
Which epoch would you use? The Unix epoch of the beginning of 1970 in UTC is common, but far from singular. Almost two dozen epochs have been used by various computer systems.

We create numeric data types for doing math rather than using bits. We create string classes to handle the nitty-gritty details of handling text rather than bare octets. So too we should create data-types and classes to handle date-time values.

The early Java teams (and IBM & Taligent before them) made an attempt with the java.util.Date and java.util.Calendar and related classes. Unfortunately, the attempt was inadequate. While date-time is inherently confusing, these classes have added even more confusion.

Joda-Time

As far as I know, the Joda-Time project was the first project to take on date-time in a thorough, competent, and successful manner. Even so, the creators of Joda-Time were not entirely satisfied. They went on to create the java.time package in Java 8, and extend that work with the threeten-extra project. Joda-Time and java.time share similar concepts but are distinct, each having some advantages.

Database Problems

Specifically, the java.util.Date & .Calendar classes lack date-only values without time-of-day and time zone. And they lack time-only values without date and time zone. Before Java 8, the Java team added the hacks known as the java.sql.Date and java.sql.Time classes which is a date-time value masquerading as a date-only. Both Joda-Time and java.time rectify that by offering LocalDate and LocalTime classes.

Another specific problem is that java.util.Date has a resolution of milliseconds, but databases frequently use microseconds or nanoseconds. In an ill-advised attempt to bridge this disparity, the early Java team created another hack, the java.sql.Timestamp class. While technically a java.util.Date subclass, it also tracks the fractional seconds to nanosecond resolution. So when converting in and out of this type you may losing or gaining the finer fractional seconds granularity without being conscious of that fact. So that might mean that values you expect to be equal are not.

Another source of confusion is the SQL data type, TIMESTAMP WITH TIME ZONE. That name is a misnomer as the time zone info is not stored. Think of the name as TIMESTAMP WITH RESPECT FOR TIME ZONE as any passed time zone offset info is used in converting the date-time value to UTC.

The java.time package with its nanosecond resolution has some specific features to better communicate date-time data with a database.

I could write much more, but such information can be gleaned from searching StackOverflow for words such as joda, java.time, sql timestamp, and JDBC.

Example using Joda-Time with JDBC with Postgres. Joda-Time uses immutable objects for thread-safety. So rather than alter an instance ("mutate"), we create a fresh instance based on the values of the original.

String sql = "SELECT now();";
…
java.sql.Timestamp now = myResultSet.getTimestamp( 1 );
DateTime dateTimeUtc = new DateTime( now , DateTimeZone.UTC );
DateTime dateTimeMontréal = dateTimeUtc.withZone( DateTimeZone.forID( "America/Montreal" ) );

Focus On UTC

Before this, I thought timestamps were by convention always in UTC. Why on earth would anyone want a localized timestamp instead of a localized representation of it? Wouldn't that be very confusing for everyone?

Indeed. The SQL standard defines a TIMESTAMP WITHOUT TIME ZONE which ignores and strips away any included time zone data. I cannot imagine the usefulness of that. This Postgres expert, David E. Wheeler, says as much in recommending always using TIMESTAMP WITH TIME ZONE. Wheeler cites one narrow technical exception (partitioning) and even then says to convert all the values to UTC yourself before saving to the database.

The best practice is to work and store data in UTC while adjusting to localized time zones for presentation to the user. There may be times when you want to remember the original date-time data in its localized time zone; if so, save that value in addition to converting to UTC.

Guidelines

The first steps to better date-time handling are avoiding java.util.Date & .Calendar, using Joda-Time and/or java.time, focusing on UTC, and learning the behavior of your specific JDBC driver and your specific database (databases vary widely in their date-time handling despite the SQL standard).

MySQL

Caveat: I don’t use MySQL (I'm a Postgres kind of guy).

According to the version 8 documentation, the two types DATETIME and TIMESTAMP differ in that the first one lacks any concept of time zone or offset-from-UTC. The second one uses any indication of time zone or offset-from-UTC accompanying an input to adjust that value to UTC, then stores it, and discards the zone/offset info.

So these two types seem to be akin to the standard SQL types:

MySQL DATETIME ≈ SQL-standard TIMESTAMP WITHOUT TIME ZONE
MySQL TIMESTAMP ≈ SQL-standard TIMESTAMP WITH TIME ZONE

For MySQL DATETIME, use the Java class LocalDateTime. That class, like that data type, purposely lacks any concept of time zone or offset-from-UTC. Use this type and class for either:

When you mean any zone or all zones, such as “Christmas starts on first moment of December 25, 2018”. That translates to different moments in different places as a new day dawns earlier in the east than in the west.
When scheduling appointments or events far enough out in the future that politicians may change the offset of the time zone, for which politicians around the world have shown a proclivity. In this usage, you must at runtime apply a time zone to dynamically calculate, but not store, a moment for display on a calendar. That way, a 15:00 dental appointment in 8 months remains at 15:00 even if politicians redefine the clock to be minutes/hours ahead or behind.

For MySQL TIMESTAMP, use the Java class Instant, as shown above. Use this type and class for moments, specific point on the timeline.

JDBC 4.2

As of JDBC 4.2 and later, we can directly exchange java.time objects with the database. Use getObject & setObject methods.

myPreparedStatement.setObject( … , Instant.now() ) ;

Retrieval.

Instant instant = myResultSet.getObject( … , Instant.class ) ;

The JDBC 4.2 specification requires a driver to support OffsetDateTime but strangely does not require support for the more common types Instant and ZonedDateTime. But converting between types is quite easy.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;
Instant instant = odt.toInstant() ;

You can then adjust that UTC value in Instant to a specific time zone for presentation to a user.

ZoneId z = ZoneId.of( "Pacific/Auckland" ) ;
ZonedDateTime zdt = instant.atZone( z ) ;

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

Java SE 8, Java SE 9, Java SE 10, and later
Built-in.
Part of the standard Java API with a bundled implementation.
Java 9 adds some minor features and fixes.
Java SE 6 and Java SE 7
Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
Android
Later versions of Android bundle implementations of the java.time classes.
For earlier Android (<26), the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

mySQL time stamp to an integer?

Welcome to Stack Overflow.

To convert a date/time to a Unix timestamp, the strtotime() function should work just fine.

You can pass many types of date/time strings to this function, however, for your use here is an example:

$datetime = "2015-06-01 12:55:24";
$timestamp = strtotime($datetime); //1433177724

Unix Timestamp?

Simply put, the Unix Timestamp is the number of seconds since the January 1, 1970 at 00:00:00 UTC. Many web programs use this timestamp to parse dates and times.

Can PHP safely use unix timestamps to query mysql database?

Unix timestamp is timezone-independent.

This is also the reason you can change this step:

use these values to convert timezone to UTC and get the timestamp

into this:

convert values to Unix timestamp

Although storing timestamps in the database (eg. MySQL) is very simple. You can make sure PHP has Unix timestamp, if you will:

save the values by using FROM_UNIXTIME() MySQL's function (give Unix timestamp as argument and you will receive datetime according to MySQL's settings),
retrieve the values by using UNIX_TIMESTAMP() MySQL's function (give the name of the field, or the value, as the argument), so you will get Unix timestamp (integer) on the basis of datetime stored in the database according to MySQL's settings.

Just remember to use TIMESTAMP column type to store timestamps. This way the time will be stored in timezone-independent manner, only displayed according to MySQL's settings.

Would there be any benefit of storing unix timestamp as unsigned integer?

Your assumption surrounding UNIX_TIMESTAMP() is a big one.

Currently, UNIX_TIMESTAMP returns 0 if you try

mysql> select unix_timestamp("2038-01-19" );
+-------------------------------+
| unix_timestamp("2038-01-19" ) |
+-------------------------------+
|                    2147468400 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp("2038-01-20");
+------------------------------+
| unix_timestamp("2038-01-20") |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set (0.00 sec)

While the storage of INTs longer than 32-bits will work, unless you know something about how the implementation of unix_timestamp(int64) will work, then the question is really more guesswork than facts.

This implies that any integer arithmetic you do will still be valid with 64-bit ints, so for finding expired sessions (timestamp + timeout < (seconds since 1970 in 64-bits)) will still work. Whether or not you can rely on from_unixtime() and unix_timestamp()-functions depend whether the solution is just to up the ante to 64-bits or if the whole world in the next 20-odd years decide to set a new epoch.

Nobody knows for sure.

How to store UNIX timestamps with MySQL

A Unix timestamp is a large integer (the number of seconds since 1970-01-01 00:00:00 UTC), so INT(11) is the correct datatype.

Unfortunately, I don't think there's any way to specify a default that will insert the current timestamp. You'll need to call UNIX_TIMESTAMP() explicitly when inserting, and use that. Function calls aren't allowed in DEFAULT specifications.

Using MySQL's Timestamp VS Storing Timestamps Directly