Which Date Time Data Type to use in Application

1

I am rewriting an old program written in Clarion with tps files. I am presently going through the dictionary to see if I want to do any changes there.
One of the files contains portcalls for vessels. Since the timesheet for a vessels portcall is more or less standardised it is hardcoded into the portcall times. Not in a separate file.
The timesheet consists currently of a bunch of time and date type fields. I plan to create the dictionary with tps for the time being, but I would like to be able to change to other fileformats like sqlite and sqlserver without to much conversion.

Can I for instance use long or real datetypes for dates and times in tps, or am I restricted to date and time fields? I must also be able to calculate duration ,i.e subtract data in fields, for instance from arrival to departure, to calculate the duration of the portstay.

Or it is best to go for another fileformat right from the start?

I wonder if the tps format is used much today or if is it oldfashioned and other file formats preferred instead?

brgds
Werner

2

The Help documents clearly state that date variables and function return values are LONG so that’s a good place to start, and they’re perfect for .tps

Example: date_return_standard_date_.htm [Clarion Community Help]

3

Hi,

You can use the LONG type, but if you pretend to use SQL in the future, it’s better to stay with DATE and TIME types…

4

Stick with dates and times in Clarion. If you do switch to SQL later, clarion will then take care of moving those into the database properly, whereas if you used LONGs you would be responsible for handling the transfer (which would probably involve formatting the dates and times as strings before sending them).

Most SQL databases also have a datetime variable type, which makes the calculation of durations easier: duration = leave_port - arrive_port. If you have the separate data and time fields you have to do the: if leave_time < arrive_time then subtract one day from the day duration. In Clarion you still have date and time fields.

5

The date and time datatypes have the advantage the they are better readable when working with tools like MS SQL Server Studio. So I am happy I choose these formats when I had to decide longs versus date and time.

I explicit prefer date and time above the datetime datatype to avoid the need to work with group/over in the dictionary. I find it easer to work.

In my experience you can use date in TPS without problems. Not worked with time in TPS but Clarion converts both to long when reading. Further date and time are fine for MS SQL. I have no experience with SQL Lite so I cannot say.

For duration calculations If you can work with stardates. That is useful when passing midnight as time starts at 0 then.

Some code I use:

FromStarDate          PROCEDURE  (REAL pStardate, *DATE pDate, *TIME pTime)
  CODE
  pDate = INT(pStarDate)
  pTime  = ((pStarDate - pDate) * 8640000) + 1
  !IF pTime < 6001 THEN pTime = 6001 .  


SetStarDate       PROCEDURE  (DATE pDate, TIME pTime)!,REAL
  CODE
  Return pDate + pTime / 8640000

Not my code, credits to Mike Hanson (I think)

6

DATE and TIME types do have the restriction that they must be valid values or are reset to zero. Like a DATE must be Zero or 4 to 999999+ (not sure of the Max).

Storing a Date as a LONG has no restrictions. So if you wanted to use -1 -2 -3 to indicate something special that would be saved. Of course it would not format correctly with @d pictures, or work nicely with SQL Server side date code.

7

Thanks all for replies.

It seems that the easiest solution would be to use date and time field types with tps.

brgds
Werner

8

In your database fields, use DATE and TIME types. In all member variables, use LONGs. Don’t be tempted to use DATE and TIME types for your memory variables, as it causes redundant conversions at runtime, and can actually cause strange behavior with edge cases.

1 Like