How to convert unix timestamp to date/time

Hi Josh,

Good question! I never thought of this…

Unfortunately, the Visual Query Builder does not support column related functions. You can only apply group functions.

As a workaround you could use the Visual Query Builder to build your query, click the Query tab to view your query and then copy/paste the query to new query without using the Visual Query Builder.

Would that work for you?

Thanks,
Peter

Yeah I think that’d work, I do see it in the query tab:

https://pastebin.com/5ZVWwD9v

I was attempting to modify using some guidance from: https://stackoverflow.com/questions/2904256/how-can-i-convert-bigint-unix-timestamp-to-datetime-in-sql-serv

but I’m not too familiar with sql (why I very much appreciate the visual builder!)
would you be able to offer any guidance on how to modify the query above to get the desired date/time format?

• This reply was modified 2 years, 5 months ago by joshdclarke.
• This reply was modified 2 years, 5 months ago by joshdclarke.
• This reply was modified 2 years, 5 months ago by joshdclarke.
• This reply was modified 2 years, 5 months ago by joshdclarke.

Hi Josh,

You can use function from_unixtime:

SELECT from_unixtime(wp_wise_chat_messages.time`) as ‘Time’
, wp_wise_chat_messages.user as ‘User’
, wp_wise_chat_messages.channel as ‘Channel’
, wp_wise_chat_messages.text as ‘Text’
FROM wp_wise_chat_messages`

You can also add a format:
from_unixtime(wp_wise_chat_messages.time, ‘%Y %D %M %H:%i:%s’)

Here is some more documentation:
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_from-unixtime

Does this help?

>>> but I’m not too familiar with sql (why I very much appreciate the visual builder!)

Great! That’s why I created the Visual Query Builder… ??

Thanks,
Peter

That worked perfectly! Very much appreciated Peter!

I can’t seem to get the formatting to work for some reason

• This reply was modified 2 years, 5 months ago by joshdclarke.

Hi Josh,

Thanks your reporting back! I think I know why the formatting did not work. I guess you copied my example into your query, right? The problem is that quotes are converted when a message is posted. Please try the format below. Just two single quotes.

'%Y %D %M %H:%i:%s'

Best regards,
Peter