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

Get Help
1

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.

2

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.
https://www.metabase.com/docs/latest/administration-guide/03-metadata-editing.html
But it’s recommended to convert your database column to datetime instead of UNIX timestamp.

1 Like
3

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.

4

@J_trida You should use stackoverflow.com 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.

5

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.
Thanks.

6

@EGetahun

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.
https://www.metabase.com/docs/latest/administration-guide/03-metadata-editing.html#casting-to-a-specific-data-type

Otherwise you can use Models if you cannot change your database:
https://www.metabase.com/docs/latest/users-guide/models.html

7

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?

8

Thanks, flamber.

Casting works very well.

9

@EGetahun

  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
10

Casting does the job perfectly and dates convert correctly.

Thanks you very much.

11

Depending on how it is configured in your data source, it sounds like there is a discrepancy between using unix timestamps with different grains, eg. seconds since epoch, milliseconds since epoch, etc.

If you send it a seconds since epoch timestamp and it is expecting milliseconds, then it will convert to some date in 1970.

In BigQuery:

SELECT TIMESTAMP_SECONDS(1679333471) 
UNION ALL
SELECT TIMESTAMP_MILLIS(1679333471)

Gives:

2023-03-20 17:31:11.000000 UTC
1970-01-20 10:28:53.471000 UTC

Multiply the unix timestamp by 1000 to get the correct timestamp (but you will lose millisecond accuracy)