MySQL – How to Select Date Based on Daylight Saving Periods

MySQLtimestamp

Imagine that I have this table:

   Id   |      Name       |       Timestamp 
________________________________________________
    1   | Bill Gates      | 2017-01-10 12:00:00
________________________________________________
    2   | Mark Zuckerberg | 2017-07-23 17:00:00

If I use a simple query during the winter time like this:
SELECT Timestamp FROM TableName WHERE name="Bill Gates" and try to print it using PHP or any other language I will get this output "2017-01-10 12:00:00".
If I use the exact same query but during the summer time (DST) I get this output "2017-01-10 13:00:00"

The Question: Is there any way to select the timestamp using the correct DST for each row based on the timestamp?
For example, if I select both rows from the table above, I will get the timestamp from line 1 as winter time and the timestamp from the second line as summer time. Can this be done with SQL only?

Thanks.

Best Answer

I'm not completely sure on your usage goal here. But the first paragraph on this link may be what your're looking for.

12.3.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME

TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp).

For any TIMESTAMP or DATETIME column in a table, you can assign the current timestamp as the default value, the auto-update value, or both:

An auto-initialized column is set to the current timestamp for inserted rows that specify no value for the column.

An auto-updated column is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. An auto-updated column remains unchanged if all other columns are set to their current values. To prevent an auto-updated column from updating when other columns change, explicitly set it to its current value. To update an auto-updated column even when other columns do not change, explicitly set it to the value it should have (for example, set it to CURRENT_TIMESTAMP).

In addition, you can initialize or update any TIMESTAMP (but not DATETIME) column to the current date and time by assigning it a NULL value, unless it has been defined with the NULL attribute to permit NULL values.

To specify automatic properties, use the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses in column definitions. The order of the clauses does not matter. If both are present in a column definition, either can occur first. Any of the synonyms for CURRENT_TIMESTAMP have the same meaning as CURRENT_TIMESTAMP. These are CURRENT_TIMESTAMP(), NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, and LOCALTIMESTAMP().

Use of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP is specific to TIMESTAMP and DATETIME. The DEFAULT clause also can be used to specify a constant (nonautomatic) default value; for example, DEFAULT 0 or DEFAULT '2000-01-01 00:00:00'.

Clarification on timestamp

In short with the exact table you have. No you can't.

The way time stamp works, is lets say you insert 5:00PM local time into your table. The very first thing MySQL does with a timestamp field is it converts that Local time it received to UTC. (eg. I'm in CST. so it would subtract 5 from my time).

At this point, what time zone it was when it was entered is lost. Instead it recalculates what time to display when it is selected in the future based on the local time of the connection (or default time zone of server) at the time of query. This allows for the Select statement to see the time stamp in terms of Your current local 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.

Answer

To accomplish what you desire, your table would also need to contain a column for storing Which time zone the timestamp was inserted FROM/WHEN, so that you could use that to format the timestamp data when it is retrieved later. (As far as I can tell, this formatting would have to be done with logic (select timestamp between dates kind of thing) based on where/when the timestamp was inserted)

Related Question