1

I am creating a database to store time entries that have been created in Clockify, I need to declare a data type for the duration field. A string is returned in the TimeIntervalDto and an example provided in the API documentation is "PT1M4S" or "PT1H30M15S". This is obviously a meaningful string if you know how to decode it.

The example given in the API documentation is:

"timeInterval": { "duration": "PT1M4S", (Example: PT1H30M15S - 1 hour 30 minutes 15 seconds) "end": "2018-06-12T14:01:41Z", "start": "2018-06-12T14:00:37Z" },

My questions are:

  1. How to I translate duration to something meaningful; and
  2. What is the maximum size I would need to cater for, assuming that I'm using varchar, or nvarchar as the data type?
teal
  • 13
  • 2

2 Answers2

1

You are working with the ISO 8601:2004(en) duration ISO format.

You have a pattern like the follow: "PnnYnnMnnDTnnHnnMnnS"

In detail:

  • The letter P represent a Period Format
  • Each letter represent a different value, for example if you want to indicate a period of two year, use 02Y.
  • For hours use the letter T.

So for your question:

If you can use C# languange before saving to database you can decode de pattern using something like this:

 String pattern = "PT1H30M15S";
 TimeSpan ts = System.Xml.XmlConvert.ToTimeSpan(pattern); //Use System.Xml because it works with ISO 8601:2004

You can look for further information on in the ISO webpage. https://www.iso.org/obp/ui/#iso:std:iso:8601:ed-3:v1:en

Martin Vecchione
  • 66
  • 2
0

If you are using Postgres Database, you can create a new column based on end and start columns. So you don't need to create a new column just for the duration as it is already implicit with the other two columns.

If you want to get the durations you could do this query:

SELECT (t.end - t.start) as "duration", t.start, t.end from t

This will return you a new column "duration" in interval type.

If you really want to create a new column you could do a SQL trigger to fill a column "duration" of type interval using "start" and "end" columns every time a new entry is added to the table.

Lukas Belck
  • 30
  • 2
  • 8