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.
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 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.
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.
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?
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)