Can't print seconds from time

Hey everyone!
I’ve been trying to print seconds in a tabular view in Metabase and simply can’t get it to work.

I’ve got a postgresql db, column has ‘time without time zone’ as type.
The data shows as ‘00:51:00’; I calculate lenght of an alarm and put it into this column.

In Metabase, I’ve tried column formatting, I’ve tried formatting in the administration pannel, I’ve tried about every time types that makes sense in Field type in the admin panel…

Even tested changing type to time with time zone for the kicks, same thing…

It seems to me that Metabase interpret time as timestamp (I can format date but this is a time type) hence why I get problems but I am certainly not the only person that is using postgre time type and want to show seconds so I ask you people here for some help :wink:

thanks a bunch

Hi @tarminyatur
Please post “Diagnostic Info” from Admin > Troubleshooting.
And can you supply a sample schema (DDL) to reproduce?

hi @flamber.

here’s the info, let me know if you need any other info. Thanks!

Diagnostic info
{
“browser-info”: {
“language”: “fr-CA”,
“platform”: “Win32”,
“userAgent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.150 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “UTF-8”,
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.10+9-LTS”,
“java.vendor”: “Red Hat, Inc.”,
“java.vendor.url”: “https://www.redhat.com/”,
“java.version”: “11.0.10”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.10+9-LTS”,
“os.name”: “Linux”,
“os.version”: “3.10.0-1127.10.1.el7.x86_64”,
“user.language”: “en”,
“user.timezone”: “America/New_York”
},
“metabase-info”: {
“databases”: [
“postgres”
],
“hosting-env”: “unknown”,
“application-database”: “postgres”,
“application-database-details”: {
“database”: {
“name”: “PostgreSQL”,
“version”: “12.5”
},
“jdbc-driver”: {
“name”: “PostgreSQL JDBC Driver”,
“version”: “42.2.18”
}
},
“run-mode”: “prod”,
“version”: {
“date”: “2021-03-03”,
“tag”: “v0.38.1”,
“branch”: “release-x.38.x”,
“hash”: “79ef63a”
},
“settings”: {
“report-timezone”: null
}
}
}

Here’s a dump.

--
-- PostgreSQL database dump
--

-- Dumped from database version 12.5
-- Dumped by pg_dump version 12.5

-- Started on 2021-03-04 19:12:15 EST

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- TOC entry 215 (class 1259 OID 16501)
-- Name: tbl_pannes_laser; Type: TABLE; Schema: public; Owner: las002
--

CREATE TABLE public.tbl_pannes_laser (
    id_panne integer NOT NULL,
    date_debut timestamp without time zone,
    date_fin timestamp without time zone,
    duree time without time zone,
    type character varying(10) DEFAULT NULL::character varying,
    cause_pannes character varying(255) DEFAULT NULL::character varying,
    complete boolean DEFAULT false,
    exclus boolean DEFAULT false
);


ALTER TABLE public.tbl_pannes_laser OWNER TO las002;

--
-- TOC entry 214 (class 1259 OID 16499)
-- Name: tbl_pannes_laser_id_panne_seq; Type: SEQUENCE; Schema: public; Owner: las002
--

CREATE SEQUENCE public.tbl_pannes_laser_id_panne_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.tbl_pannes_laser_id_panne_seq OWNER TO las002;

--
-- TOC entry 3747 (class 0 OID 0)
-- Dependencies: 214
-- Name: tbl_pannes_laser_id_panne_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: las002
--

ALTER SEQUENCE public.tbl_pannes_laser_id_panne_seq OWNED BY public.tbl_pannes_laser.id_panne;


--
-- TOC entry 3605 (class 2604 OID 16504)
-- Name: tbl_pannes_laser id_panne; Type: DEFAULT; Schema: public; Owner: las002
--

ALTER TABLE ONLY public.tbl_pannes_laser ALTER COLUMN id_panne SET DEFAULT nextval('public.tbl_pannes_laser_id_panne_seq'::regclass);


--
-- TOC entry 3741 (class 0 OID 16501)
-- Dependencies: 215
-- Data for Name: tbl_pannes_laser; Type: TABLE DATA; Schema: public; Owner: las002
--

COPY public.tbl_pannes_laser (id_panne, date_debut, date_fin, duree, type, cause_pannes, complete, exclus) FROM stdin;
53      2021-02-27 06:49:00     2021-02-27 07:40:00     00:51:00        ANC     \N      f       f
54      2021-02-27 06:46:00     2021-02-27 07:40:00     00:54:00        EE      \N      f       f
55      2021-02-27 07:46:00     2021-02-27 07:48:00     00:02:00        EE      \N      f       f
\.


--
-- TOC entry 3748 (class 0 OID 0)
-- Dependencies: 214
-- Name: tbl_pannes_laser_id_panne_seq; Type: SEQUENCE SET; Schema: public; Owner: las002
--

SELECT pg_catalog.setval('public.tbl_pannes_laser_id_panne_seq', 55, true);


--
-- TOC entry 3611 (class 2606 OID 16510)
-- Name: tbl_pannes_laser tbl_pannes_laser_pkey; Type: CONSTRAINT; Schema: public; Owner: las002
--

ALTER TABLE ONLY public.tbl_pannes_laser
    ADD CONSTRAINT tbl_pannes_laser_pkey PRIMARY KEY (id_panne);


-- Completed on 2021-03-04 19:12:19 EST

--
-- PostgreSQL database dump complete
--

Tested some things and here’s some complementaty information, hope this will help:
-Metabase won’t show the seconds but if I do sum of the time column, it will add seconds correctly.
-Still can’t find a way to show seconds in Editor mode.
-Same behavior with Interval type.
-In SQL mode, to_char(column,‘HH24:MI:SS’) will show seconds but that forces users to go in SQL mode and format the time column.

Same behavior on rhel8/mysql with same time types.
{
“browser-info”: {
“language”: “fr-CA”,
“platform”: “Win32”,
“userAgent”: “Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; .NET4.0C; .NET4.0E; .NET CLR 2.0.50727; .NET CLR 3.0.30729; .NET CLR 3.5.30729; Zoom 3.6.0; wbx 1.0.0; wbxapp 1.0.0; rv:11.0) like Gecko”,
“vendor”: “”
},
“system-info”: {
“file.encoding”: “UTF-8”,
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “1.8.0_262-b10”,
“java.vendor”: “Oracle Corporation”,
“java.vendor.url”: “http://java.oracle.com/”,
“java.version”: “1.8.0_262”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “25.262-b10”,
“os.name”: “Linux”,
“os.version”: “4.18.0-193.14.3.el8_2.x86_64”,
“user.language”: “en”,
“user.timezone”: “America/New_York”
},
“metabase-info”: {
“databases”: [
“mysql”
],
“hosting-env”: “unknown”,
“application-database”: “mysql”,
“application-database-details”: {
“database”: {
“name”: “MySQL”,
“version”: “8.0.17”
},
“jdbc-driver”: {
“name”: “MariaDB Connector/J”,
“version”: “2.6.2”
}
},
“run-mode”: “prod”,
“version”: {
“tag”: “v0.37.1”,
“date”: “2020-11-12”,
“branch”: “release-x.37.x”,
“hash”: “ba57ab6”
},
“settings”: {
“report-timezone”: null
}
}
}

@tarminyatur Okay, now I understand. Metabase does not support interval/duration types:
https://github.com/metabase/metabase/issues/8875 - upvote by clicking :+1: on the first post
The workaround would be to create a database View, where you have made the char conversion.

1 Like

cool thx for your time :wink: