FROM_UNIXTIME returns incorrect value

[Lee Bieber]

drizzle> SELECT FROM_UNIXTIME(24*3600);
+------------------------+
| FROM_UNIXTIME(24*3600) |
+------------------------+
| 1970-01-02 03:00:00 |
+------------------------+
1 row in set (0.00 sec)

Seems that we are off by 2 hours, It should return
1970-01-02 01:00:00

[Jay Pipes (jaypipes)]

Setting to confirmed/critical. Any wrong data is a critical bug.

[Arjen Lentz (arjen-lentz)]

> drizzle> SELECT FROM_UNIXTIME(24*3600);
> +------------------------+
> | FROM_UNIXTIME(24*3600) |
> +------------------------+
> | 1970-01-02 03:00:00 |
> +------------------------+
> 1 row in set (0.00 sec)
>
> Seems that we are off by 2 hours, It should return 1970-01-02 01:00:00

Actually I think it should return 1970-01-02 00:00:00 (midnight not
1am), without other factors affecting it.

I mention "other factors" because I think this is simply timezone
conversion.
 From the manual (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
)
"The CURRENT_TIMESTAMP(), CURRENT_TIME(), CURRENT_DATE(), and
FROM_UNIXTIME() functions return values in the connection's current
time zone, which is available as the value of the time_zone system
variable."

I just ran this in MySQL 5.1.30:

mysql> SELECT FROM_UNIXTIME(24*3600);
+------------------------+
| FROM_UNIXTIME(24*3600) |
+------------------------+
| 1970-01-02 10:00:00 |
+------------------------+
1 row in set (0.20 sec)

My laptop lives in GMT+10, so I think that works out properly.

Cheers,
Arjen.
--
Arjen Lentz, Director @ Open Query (http://openquery.com.au)
MySQL Training from $475/day, DBA/Support from $249/month

My blog is at http://arjen-lentz.livejournal.com
OurDelta: free enhanced builds for MySQL @ http://ourdelta.org

[Jay Pipes (jaypipes)]

Arjen, we don't support any timezones, so it should be 0 offset (UTC). You're right about it being 3 hours off, not 2.

[Jay Pipes (jaypipes)]

GDB output:
2543 lex_start(session);
(gdb) break Item_func_from_unixtime::get_date
Breakpoint 2 at 0x5ddb07: file function/time/from_unixtime.cc, line 71.
(gdb) c
Continuing.

Breakpoint 2, Item_func_from_unixtime::get_date (this=0x1e27ee8,
    ltime=0x444405a0, fuzzy_date=1) at function/time/from_unixtime.cc:71
71 uint64_t tmp= (uint64_t)(args[0]->val_int());
(gdb) where
#0 Item_func_from_unixtime::get_date (this=0x1e27ee8, ltime=0x444405a0,
    fuzzy_date=1) at function/time/from_unixtime.cc:71
#1 0x000000000043b97b in Item::send (this=0x1e27ee8, protocol=0x1e1e190,
    buffer=0x44440640) at item.cc:1649
#2 0x000000000048ffd5 in select_send::send_data (this=0x1e28040,
    items=@0x1e1f5f0) at session.cc:1348
#3 0x0000000000510505 in JOIN::exec (this=0x1e28060) at sql_select.cc:2065
#4 0x000000000050d59a in mysql_select (session=0x1e1ddf0,
    rref_pointer_array=0x1e1f6d0, tables=0x0, wild_num=0, fields=@0x1e1f5f0,
    conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
    select_options=2147764224, result=0x1e28040, unit=0x1e1f260,
    select_lex=0x1e1f4f8) at sql_select.cc:2707
#5 0x0000000000511f28 in handle_select (session=0x1e1ddf0, lex=0x1e1f240,
    result=0x1e28040, setup_tables_done_option=0) at sql_select.cc:298
#6 0x00000000004dc095 in execute_sqlcom_select (session=0x1e1ddf0,
    all_tables=0x0) at sql_parse.cc:2278
#7 0x00000000004dcd09 in mysql_execute_command (session=0x1e1ddf0)
    at sql_parse.cc:1097
#8 0x00000000004e0278 in mysql_parse (session=0x1e1ddf0,
    inBuf=0x1e27ca0 "SELECT FROM_UNIXTIME(24*3600)", length=29,
    found_semicolon=0x44441e18) at sql_parse.cc:2573
#9 0x00000000004e0940 in dispatch_command (command=COM_QUERY,
    session=0x1e1ddf0, packet=0x1e1fc71 "", packet_length=29)
---Type <return> to continue, or q <return> to quit---
    at sql_parse.cc:593
#10 0x00000000004e1574 in do_command (session=0x1e1ddf0) at sql_parse.cc:414
#11 0x000000000048afb9 in libevent_thread_proc (arg=0x0) at scheduler.cc:535
#12 0x00007ff2ce2ed3ea in start_thread () from /lib/libpthread.so.0
#13 0x00007ff2cd6abc6d in clone () from /lib/libc.so.6
#14 0x0000000000000000 in ?? ()
(gdb) list
66 }
67
68 bool Item_func_from_unixtime::get_date(DRIZZLE_TIME *ltime,
69 uint32_t fuzzy_date __attribute__((unused)))
70 {
71 uint64_t tmp= (uint64_t)(args[0]->val_int());
72 /*
73 "tmp > TIMESTAMP_MAX_VALUE" check also covers case of negative
74 from_unixtime() argument since tmp is unsigned.
75 */
(gdb) print ltime->days
There is no member named days.
(gdb) print ltime->day
$1 = 25951120
(gdb) print ltime->second
$2 = 0
(gdb) print ltime->second_part
$3 = 25954376
(gdb) print ltime->hour
$4 = 0
(gdb) print ltime->minute
$5 = 25951120
(gdb) print ltime->year
$6 = 25951072
(gdb) print ltime->time_type
$7 = DRIZZLE_TIMESTAMP_DATE
(gdb) print ltime->neg
$8 = 240
(gdb) print ltime->month
$9 = 0
(gdb) print args[0]->val_int()
$10 = 86400
(gdb) s
Item_func_numhybrid::val_int (this=0x1e27e08) at function/numhybrid.cc:116
116 assert(fixed == 1);
(gdb) n
117 switch (hybrid_...

[Jay Pipes (jaypipes)]

The fix is to set the session->variables.time_zone= my_tz_UTC. I am committing the fix locally. Will merge shortly.

[Jay Pipes (jaypipes)]

Fix committed locally. Will propose merge.

[Jay Pipes (jaypipes)]

Wow, good thing I tested all the other tests... :) Making that simple change breaks lots of stuff. Marking back into IN PROGRESS.

[Jay Pipes (jaypipes)]

This is no longer a bug. Timezones are supported in Drizzle and FROM_UNIXTIME() shall adapt the returned timestamp-formattted string to the server's timezone.