I read this and As per MySQL documentation for Timestamp:
It can hold values starting at '1970-01-01 00:00:01' (UTC) to '2038-01-19 05:14:07' (UTC) . This range is caused by MariaDB storing the TIMESTAMP values as the number of seconds since '1970-01-01 00:00:00' (UTC).
so all the timestamp related operations are done in UNIX_TIMESTAMP as there is no timezone info stored in timestamp. That was my understanding.
My current time zone: IST (+05:30) After midnight, when date changed in IST but not in UTC I did an insert operation. I thought if I do a DATE(now()) for IST it should show the yesterday's stored record as I thought UNIX_TIMESTAMP will be used for timestamp comparison which will remain same.
In below code block you can see the timezone offset as +05:30 which is for IST. Record against ID = 5 was inserted at 13th Feb,2017 00:04 pm. But in UTC_STAMP would have been somewhere 12th Feb,2017 18hr34min(5hr30min back). So date is not changed in UTC. I did a select statement for date = 13th Feb 2017, I thought I would I get (1,2,3,5) records as result because UTC representation of 13th Feb 2017,00:00 in IST still falls under UTC date of 12thFeb,2017 . But I got only record against ID = 5.
Q.1 In short I was thinking that value = 2017-02-13 will be converted to UNIX_TIMESTAMP(a numerical value) and then comparison occurs. What I am missing ? or else mention the steps taken by db to generate the below result ? I hope I was able explain myself.
Q.2 How does java.sql.Timestamp executes ? It works something like mentioned in code block or it first converts timestamp values to unix_timestamp and then do the conversion or is it database internal implementation compares long values of timestamp ?
MariaDB [test]>SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | +05:30 |
+--------------------+---------------------+
MariaDB [test]> desc t;
+-------+-----------+------+-----+-------------------+---------------- -------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id | int(11) | YES | | NULL | |
| ts | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+--------------- --------------+
MariaDB [test]> select * from t;
+------+---------------------+
| id | ts |
+------+---------------------+
| 1 | 2017-02-12 22:10:35 |
| 2 | 2017-02-12 22:10:35 |
| 3 | 2017-02-12 22:13:06 |
| 4 | 2001-07-22 12:12:12 |
| 5 | 2017-02-13 00:04:01 |
+------+---------------------+
MariaDB [test]> select * from t where date(ts) = '2017-02-13';
+------+---------------------+
| id | ts |
+------+---------------------+
| 5 | 2017-02-13 00:04:01 |
+------+---------------------+
MariaDB [test]> set time_zone = '+00:00';
MariaDB [test]> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | +00:00 |
+--------------------+---------------------+
MariaDB [test]> select * from t;
+------+---------------------+
| id | ts |
+------+---------------------+
| 1 | 2017-02-12 16:40:35 |
| 2 | 2017-02-12 16:40:35 |
| 3 | 2017-02-12 16:43:06 |
| 4 | 2001-07-22 06:42:12 |
| 5 | 2017-02-12 18:34:01 |
+------+---------------------+
MariaDB [test]> select * from t where date(ts) = '2017-02-12';
+------+---------------------+
| id | ts |
+------+---------------------+
| 1 | 2017-02-12 16:40:35 |
| 2 | 2017-02-12 16:40:35 |
| 3 | 2017-02-12 16:43:06 |
| 5 | 2017-02-12 18:34:01 |
+------+---------------------+
EDIT1: I tried using database server with UTC timezone and IST as application server. After midnight, when IST changed its date and UTC didn't- I repeated the insert and create operations as mentioned above.Below are the records and info:
MariaDB [test]> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| UTC | UTC |
+--------------------+---------------------+
1 row in set (0.30 sec)
MariaDB [test]> select * from t ;
+----+---------------------+
| id | ts |
+----+---------------------+
| 1 | 2017-02-13 19:22:15 |
| 2 | 2017-02-13 19:22:15 |
| 3 | 2017-02-13 19:21:40 |
| 4 | 2001-07-22 12:12:12 |
| 5 | 2017-02-14 00:56:13 |
+----+---------------------+
5 rows in set (0.40 sec)
MariaDB [test]> select UTC_TIMESTAMP;
+---------------------+
| UTC_TIMESTAMP |
+---------------------+
| 2017-02-13 19:21:22 |
+---------------------+
1 row in set (0.38 sec)
And used JDBC, to get the response:
SELECT * FROM t WHERE date(ts) = date(:currentDate);
where, currentDate = Timestamp.from(Instant.now()); from Java
Response was:
[
{
"id": 1,
"timestamp1": 1486993935000
},
{
"id": 2,
"timestamp1": 1486993935000
},
{
"id": 3,
"timestamp1": 1486993900000
}
]
why record(id=5) did not came ? Doesn't it mean that it looks for Visual Representation rather than extract date from UTC_TIMESTAMP numerical value if it would have done that it would have fetched record with id = 5.
