Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

New feature: Display UNIX timestamps as date/time values

ansgar's profile image ansgar posted 6 years ago in News Permalink
One of the best new ideas in my eyes came from quelbs. Using an integer column for storing UNIX timestamp values is a wide spread approach in the world of web development. For example in a Typo3 database, all date/time handling is done with UNIX timestamps. Unfortunately, for MySQL this is of course a normal integer value, with no meaning of being a date/time value.

Now, HeidiSQL allows the user to explicitly set a column to "This is a UNIX timestamp" via right click menu. Once activated, integers are internally converted to and displayed as human readable date/time values. Editing such values also uses the inplace editor for date values.
1 attachment(s):
  • timestamp-datetime
r569594043's profile image r569594043 posted 6 years ago Permalink
great!!!happy
quelbs's profile image quelbs posted 6 years ago Permalink
Thank you for your quick implementation of this idea.
Michael
Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
harry0815's profile image harry0815 posted 6 years ago Permalink
For all of us fuzzing around with Unix timestamps this is a really great addition, saves a lot of time and confusions. Thanks a lot for this!

However... ;)
For some of us the second precision is not good enough, so we store the timestamp in millisecond precision. Unfortunately this is not recognized at the moment.

Example which you can check e.g. with http://www.epochconverter.com/ :
o 1378391224 is the simple UNIX timestamp which resolves to 05.09.2013 14:27:04
o 1378391224123 adds milliseconds which resolves to 05.09.2013 14:24:04.123

Would it be possible to extend this great feature to also recognize timestamps in microseconds? Maybe this can be auto-detected (larger numbers) or added a sub-menu option?

If this makes sense, I would be happy to propose an improvement and write a request similar to #3215.
harry0815's profile image harry0815 posted 6 years ago Permalink
Sorry, I meant milliseconds. But there might be also folks outside who store the timestamp in microseconds ;)
justpusher's profile image justpusher posted 6 years ago Permalink
Thank you very much! Very needed feature!
maXus's profile image maXus posted 6 years ago Permalink
Great, but is it possible to do the same with IP's? (INET_NTOA and INET_ATON)

Gruß Marcus
justpusher's profile image justpusher posted 6 years ago Permalink
+1 for inet_ntoa.
ansgar's profile image ansgar posted 6 years ago Permalink
Well, not yetsmileDifferent feature. File a request in the tracker?
justpusher's profile image justpusher posted 6 years ago Permalink
I've created issue 3402 regarding IPs similar to existing issue 3215 regarding unix timestamps.
Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
maXus's profile image maXus posted 6 years ago Permalink
Top, thanks!
maXus's profile image maXus posted 6 years ago Permalink
is there a solution to copy the timestamps as timestamp, not as value time? currently is use timestamp in a row, but i can copy only the full date/time, not the real timestamp.
ansgar's profile image ansgar posted 6 years ago Permalink
Copying UNIX timestamp as integers was fixed some time ago. You should update your HeidiSQL to the latest build.
maXus's profile image maXus posted 6 years ago Permalink
Ah ok, I wait for the stable version. Thank you.
ansgar's profile image ansgar posted 6 years ago Permalink
... and I should release one in December. Many bugs fixed already, and many users don't get these fixes. According to my download statistics, the major part of HeidiSQL users only updates when major or minor releases are out.
md2perpe's profile image md2perpe posted 5 years ago Permalink
What time zone is used for conversion from timestamp to date and time?
tkn777's profile image tkn777 posted 4 years ago Permalink
Is it planed to detect if timestamp field has miiliseconds (as posted by harry0815)?
sugarthunder's profile image sugarthunder posted 4 years ago Permalink

Is is possible to support unixtime with milliseconds? Like so: 1455553608.158 Or even so: 1455553608158

Ziggi's profile image Ziggi posted 3 weeks ago Permalink

Well - but for queries this option is greyed-out! It's purposeless!

image description

ansgar's profile image ansgar posted 3 weeks ago Permalink

Please file a new issue in the bugtracker for support in query results.

justpusher's profile image justpusher posted 2 weeks ago Permalink

In queries you can easily get human-readable time by adding FROM_UNIXTIME(field). I think this isn't a high-priority feature.

Please login to leave a reply, or register at first.