How can I convert Unix TimeStamp data to datetime format (dd/mm/yyyy)?

I have in my Mysql database a table with the data values in Unix TimeStamp (milliseconds). Is there any form of conversion so that when displaying the query results it appears in the data format dd / mm / yyyy?

For example, I want to program an indicator of processes started per month, so I need to count all the processes in my database for each of the 12 months of the year. The user can select a month of interest, in which case the number of processes initiated per day (0 - 31) for that month must appear.

I believe that the first step is to convert the Unix format to date and after working with these to perform counting and filters.

Hi @J_trida
You can change Field Type to “UNIX Timestamp” in Admin > Data Model > (database) > (table) > (column), which will make the field show as regular datetime field.
But it’s recommended to convert your database column to datetime instead of UNIX timestamp.

1 Like

This answer does not include my doubt. I am not interested in the data display format, but in the operations carried out on them.

I believe my question is more specific to SQL programming functions, since I have data in the Unix TimeStamp type, but in the result of my operations I want to display it in the date format.
The case I’m trying to solve is to count the number of processes initiated for each month (regardless of the year), that is:

month   count
Jan       23
Feb       18
mar       45
apr       16
may       54
jun       48
July      32
aug       36
sept      22
oct       08
nov       12
dec       18

The big problem I have is not being able to identify the starting month, because my date is in the Unix TimeStamp format (milliseconds - type/BigInt). If you have seen something similar or know a solution and possible to share, thank you.

@J_trida You should use or similar forum dedicated to the database you’re using, since it is not related to Metabase - they will be able to provide much better help.

I attempted to convert the numeric UNIX Timestamp to regular datetime format using admin settings as described. However, the dates converted are not correct. Dates in 2021/22 show up as if they are in 1970s. I am not sure if it’s a bug in Metabase or not. Any help on this is much appreciated.
Converting the date format of the column in the database is not feasible.


Post "Diagnostic Info" from Admin > Troubleshooting.

Please provide an example of a value, and your database column type.

Did you follow the new way of casting? Things have changed since forum topic was created.

Otherwise you can use Models if you cannot change your database:

When converting dates from UNIX Timestamp to regular datetime using Admin settings, Data Model>(database)>(table)>(column)>(column type), I think Metabase divides the UNIX Timestamp by 1,000,000 then calls the function FROM_UNIXTIME(), i.e.

FROM_UNIXTIME('date_in_unix_timestamp'/ 1000000) AS date_in_regular_datetime.

That’s why the converted dates are not correct. I wonder why Metabase does that?

Thanks, flamber.

Casting works very well.


  1. Post "Diagnostic Info" from Admin > Troubleshooting.
  2. Post an example value you currently have in date_in_unix_timestamp
  3. Post the actual database column type of date_in_unix_timestamp

Casting does the job perfectly and dates convert correctly.

Thanks you very much.