Differences between timestamp and datetime in MySQL

When it is good to use datetime or timestamp in MySQL? 
Sergey Siminskiy's picture
Sergey Siminskiy
10 August

Differences between DATETIME or a TIMESTAMP can be hard to understand for a beginner MySQL programmer. Let's try to figure out, what command and where should be used.

The  DATETIME, and TIMESTAMP types are related. MySQL recognizes DATETIME, and TIMESTAMP values in several formats.

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07'UTC

DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. In particular, any fractional part in a value inserted into a DATETIME or TIMESTAMP column is stored rather than discarded. With the fractional part included, the format for these values is 'YYYY-MM-DD HH:MM:SS[.fraction]', the range for DATETIME values is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999', and the range for TIMESTAMP values is '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'.

The TIMESTAMP and DATETIME data types offer automatic initialization and updating to the current date and time. 

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable.

Invalid DATEDATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00').

TIMESTAMP in MySQL 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 need to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native 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.

Elixir Ecto 2.2.0-rc.0 released

New update of Elixir database tool is available now 
Sergey Siminskiy's picture
Sergey Siminskiy
09 August

What is Ecto?

Ecto is a domain specific language for writing queries and interacting with databases in Elixir. Ecto provides a standardised API and a set of abstractions for talking to all the different kinds of databases, so that Elixir developers can query whatever database they’re using by employing similar contructs. 

Ecto is compatible with:

  • PostgreSQL
  • MySQL
  • c    

What's new in 2.2.0-rc.0?

This release adds many improvements to Ecto with a handful of bug fixes.

Changelist.

  • Ecto now supports specifying fields sources. 
  • [Ecto.Adapters] Accept IO data from adapters to reduce memory usage when performing large queries
  • [Ecto.Adapters.SQL] Also add Ecto.Repo.to_sql/2 to Ecto.Repo based on SQL adapters
  • [Ecto.Adapters.Postgres] Use the "postgres" database for create/drop database commands
  • [Ecto.Adapters.MySQL] Use TCP connections instead of MySQL command client to create & drop database
  • [Ecto.Changeset] Support action: :ignore in changeset which is useful when casting associations and embeds and one or more children need to be rejected/ignored under certain circumstances
  • [Ecto.Changeset] Add :repo_opts field to Ecto.Changeset which are given as options to to the repository whenever an operation is performed
  • [Ecto.Changeset] Add apply_action/2
  • [Ecto.Changeset] Add prefix constraint name checking to constraint validations
  • [Ecto.Changeset] Allow assocs and embeds in change/2 and put_change/3 - this gives a more generic API for developers to work that does not require explicit knowledge of the field type
  • [Ecto.Migration] Add reversible execute/2 to migrations
  • [Ecto.Migration] Add :migration_timestamps and :migration_primary_key to control the migration defaults from the repository
  • [Ecto.Migrator] Allow migration/rollback to log SQL commands via the --log-sql flag
  • [Ecto.LogEntry] Add :caller_pid to the Ecto.LogEntry struct
  • [Ecto.Query] Allow map updates in subqueries
  • [Ecto.Query] Support fragment and field access in type/2 in select
  • [Ecto.Query] Add select_merge/3 as a composable API for selects
  • [Ecto.Repo] Implement :returning option on insert
  • [Ecto.Repo] Add ON CONSTRAINT support to :conflict_target on insert and insert_all
  • [Ecto.Repo] Raise MultiplePrimaryKeyError when primary key is not unique on DB side
  • [Ecto.Schema] Validate schemas after compilation - this helps developers catch early mistakes such as foreign key mismatches early on
  • [Ecto.Type] Allow adapters to pass Date, Time, NaiveDateTime and DateTime on load if desired
  • [Ecto.UUID] Allow casting binary UUIDs
  • [mix ecto.drop] Add --force
  • [mix ecto.load] Add --force and prompt user to confirm before continuing in production

Bug fixes

  • [Ecto.Changeset] Remove the field from changes if it does not pass validate_required
  • [Ecto.Changeset] Raise if changeset struct does not match relation
  • [Ecto.Query] Consistently raise if nil is interpolated on the right side of in
  • [Ecto.Query] Properly expand macros in select
  • [Ecto.Query] Support or_having in keyword query
  • [Ecto.Query] Properly count the parameters when using interpolation inside a select inside a subquery
  • [Ecto.Repo] Set struct prefix on insertdelete, and update when the prefix is given as an option
  • [Ecto.UUID] Validate UUID version on casting
  • [mix ecto.*] Make sure Logger is rebootted when running ecto tasks
  • [mix ecto.*] No longer mark tasks as recursive and instead collect all repositories upfront. This fixes a bug where migration and rollback commands for a given repository could be executed multiple times from an umbrella project

Deprecations

  • [Ecto.DateTime] Ecto.DateTime as well as Ecto.Date and Ecto.Time are deprecated in favor of :naive_datetime:date and :time respectively
  • [Ecto.Repo] Using {:system, env} to configure the repository URL is deprecated in favor of a custom init/2 callback

You can learn more at GitHub