How to set a timestamp to a MySQL variable?

Question

Couldn't figure out the reason why the @previousEndTime variable is having only the YEAR value.

Sample Data on my test_table:

| id |   name |                     start |                       end |
|----|--------|---------------------------|---------------------------|
|  2 | test 1 | January, 01 2016 12:00:00 | January, 01 2016 13:00:00 |
|  3 | test 1 | January, 02 2016 11:00:00 | January, 02 2016 12:00:00 |
|  5 | test 1 | January, 03 2016 15:00:00 | January, 03 2016 16:00:00 |
|  6 | test 2 | January, 01 2016 10:00:00 | January, 01 2016 11:00:00 |
|  7 | test 2 | January, 02 2016 17:00:00 | January, 02 2016 18:00:00 |



SELECT
        id,
        name,
        @previousEndTime,
        @previousEndTime := end     
    FROM
        test_table, (SELECT @previousEndTime := 0) var
    ORDER BY name, id;

Current Result:

| id |   name | @previousEndTime | @previousEndTime := end |
|----|--------|------------------|-------------------------|
|  2 | test 1 |                0 |     2016-01-01 13:00:00 |
|  3 | test 1 |             2016 |     2016-01-02 12:00:00 |
|  5 | test 1 |             2016 |     2016-01-03 16:00:00 |
|  6 | test 2 |             2016 |     2016-01-01 11:00:00 |
|  7 | test 2 |             2016 |     2016-01-02 18:00:00 |

Expected Result:

| id |   name |     @previousEndTime| @previousEndTime := end |
|----|--------|---------------------|-------------------------|
|  2 | test 1 |                0    |     2016-01-01 13:00:00 |
|  3 | test 1 |2016-01-01 13:00:00  |     2016-01-02 12:00:00 |
|  5 | test 1 |2016-01-02 12:00:00  |     2016-01-03 16:00:00 |
|  6 | test 2 |2016-01-03 16:00:00  |     2016-01-01 11:00:00 |
|  7 | test 2 |2016-01-01 11:00:00  |     2016-01-02 18:00:00 |

Please check this FIDDLE

Note:

The other ways I've tried:

@previousEndTime := CONCAT('',end,'')

@previousEndTime := TIMESTAMP(CONCAT('',end,''))

score 2 · Accepted Answer · answered May 18 '16 at 03:36

Take a look of this;)

SQL Fiddle

MySQL 5.6 Schema:

DROP TABLE IF EXISTS `test_table`;
CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8 NOT NULL,
  `start` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `end` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);
INSERT INTO `test_table` VALUES ('2', 'test 1', '2016-01-01 12:00:00', '2016-01-01 13:00:00');
INSERT INTO `test_table` VALUES ('3', 'test 1', '2016-01-02 11:00:00', '2016-01-02 12:00:00');
INSERT INTO `test_table` VALUES ('5', 'test 1', '2016-01-03 15:00:00', '2016-01-03 16:00:00');
INSERT INTO `test_table` VALUES ('6', 'test 2', '2016-01-01 10:00:00', '2016-01-01 11:00:00');
INSERT INTO `test_table` VALUES ('7', 'test 2', '2016-01-02 17:00:00', '2016-01-02 18:00:00');

Query 1:

SELECT
    id,
    name,
    if(@previousEndTime='0000-00-00 00:00:00',0,@previousEndTime),
    @previousEndTime := end     
FROM
    test_table,
    (
        SELECT
            @previousEndTime := '0000-00-00 00:00:00'
    ) var
ORDER BY name, id

Results:

| id |   name | if(@previousEndTime='0000-00-00 00:00:00',0,@previousEndTime) | @previousEndTime := end |
|----|--------|---------------------------------------------------------------|-------------------------|
|  2 | test 1 |                                                             0 |     2016-01-01 13:00:00 |
|  3 | test 1 |                                           2016-01-01 13:00:00 |     2016-01-02 12:00:00 |
|  5 | test 1 |                                           2016-01-02 12:00:00 |     2016-01-03 16:00:00 |
|  6 | test 2 |                                           2016-01-03 16:00:00 |     2016-01-01 11:00:00 |
|  7 | test 2 |                                           2016-01-01 11:00:00 |     2016-01-02 18:00:00 |

Thank you Reno. is it something like : During the initialization part the data type of variable is determined? — Anonymous One, May 18 '16 at 04:04
Yes, u r right. Take a look of [User-Defined Variables](http://dev.mysql.com/doc/refman/5.7/en/user-variables.html). — Blank, May 18 '16 at 04:54

Hytool · Answer 2 · 2016-05-18T03:42:52.907

2

try this,

    SELECT
        id,
        name,
        @previousEndTime,
        @previousEndTime := end     
    FROM
        test_table,
        (
            SELECT
                @previousEndTime := '0000-00-00 00:00:00'
        ) var
    ORDER BY name, id;

MySQL displays YEAR values in YYYY format, with a range of 1901 to 2155, or 0000.

As a 1- or 2-digit string in the range '0' to '99'. MySQL converts values in the ranges '0' to '69' and '70' to '99' to YEAR values in the ranges 2000 to 2069 and 1970 to 1999.

The result of inserting a numeric 0 has a display value of 0000 and an internal value of 0000. To insert zero and have it be interpreted as 2000, specify it as a string '0' or '00'.

http://dev.mysql.com/doc/refman/5.7/en/year.html

edited May 18 '16 at 03:42

answered May 18 '16 at 03:37

Hytool

1,358
1
7
22

is it something like : During the initialization part the data type of variable is determined? – Anonymous One May 18 '16 at 04:04
yes, the datatype of the derived column is initiated at the first record ! – Hytool May 18 '16 at 04:30
Thanks a lot. Since Reno answered first I should accept his answer. All I can do is to upvote your answer. Thanks buddy. – Anonymous One May 18 '16 at 05:29

How to set a timestamp to a MySQL variable?

2 Answers2