Storing PureBasic internal dates in SQL

[RichAlgeni]

I've been tasked with creating a new data storage and reporting system that I am writing in PureBasic and PostgreSql. Part of the requirements will be to store starting and ending dates and times. I've created columns using the TIMESTAMP data type. While I can use the PostgreSql age() function to determine the interval. However, I was wondering if anyone has found it helpful to store Purebasic's Date() function as an integer? My thoughts are that this would be helpful for calculations.

Does anyone have any thoughts?

[buddymatkona]

It may not be exactly what you had in mind but I use the PB DATE() frequently with a PB structure just like time.SYSTEMTIME. The additional parts list makes it work directly with lots of things.

Code: Select all

Structure PBSystemTime
  wYear.w : wMonth.w;
  wDayOfWeek.w : mDay.w; mDay is Day of Month
  wHour.w : wMinute.w : wSecond.w : wMilliseconds.w;
EndStructure

Macro LongMonthName(MonthNum) ; from a post by Mohawk70
  StringField("January February March April May June July August September October November December",MonthNum," ")
EndMacro

Macro LongDayName(DayNum) ; from a post by Mohawk70
  StringField("Sunday Monday Tuesday Wednesday Thursday Friday Saturday",DayNum," ")
EndMacro

Procedure GetPBTime(*Now.PBSystemTime,DateNum.i) ; PB replacement for GetLocalTime_(time.SYSTEMTIME)
  With *Now
    \wYear = Year(DateNum): \wMonth =  Month(DateNum);
    \wDayOfWeek = DayOfWeek(DateNum) + 1 : \mDay = Day(DateNum); Day of Month
    \wHour = Hour(DateNum) : \wMinute = Minute(DateNum) : \wSecond = Second(DateNum) :\wMilliseconds = 0; Elapsed milliseconds can be added here if available
  EndWith
EndProcedure

Procedure.s TimeDateStr( *Now.PBSystemTime,PBdate.i) ; Returns TimeDate$ in the form     12:01 AM Monday Jan 1, 2013
  Define AMPM.s,Hr.s,Min.s,TimeString.s,WeekDayName.s,DateString.s
  GetPBTime(*Now.PBSystemTime,PBDate); works like GetLocalTime_(time.SYSTEMTIME
  AMPM.s = "AM" ; Convert 24 Hr time to 12 Hr time
  If *Now\wHour > 11 : AMPM = "PM" : EndIf
  *Now\wHour = (*Now\wHour + 11 ) % 12 + 1
  Hr.s=RSet(Str(*Now\wHour),2)
  Min.s=RSet(Str(*Now\wMinute),2,"0")
  TimeString.s = " " + Hr + ":" + Min + " " + AMPM + "    "
  WeekDayName.s = LongDayName(*Now\wDayOfWeek)
  DateString.s = FormatDate("   "+LongMonthName(*Now\wMonth)+" %dd, "+"%yyyy",PBdate)
  ProcedureReturn  TimeString + WeekDayName + DateString
EndProcedure

Example :

Code: Select all

DrawTimeDate(*RoundBoxPtr,TimeDateStr(Now.PBSystemTime,Date()))

[RichAlgeni]

I appreciate the quick reply Buddy, but maybe I need to explain myself better. I am writing two Timestamp's to Sql, in this case PostgreSql.

Code: Select all

INSERT INTO status_log_table
(log_number, status_from, status_from_date_time, status_to, status_to_date_time)
VALUES (103, 1, '2013-05-29  06:00:00', 2, '2013-05-29  18:03:33');

The interval for this would be 12:03:33, which in PureBasic equates to 43413. I could use the PostgreSql age function: age('2013-05-29 18:03:33', '2013-05-29 06:00:00') inside a 'select', which would return '12:03:33', which I could then capture, and convert to the PureBasic number 43413. Would it make sense to store 43413 inside a Sql integer value, so that when I do a select, I wouldn't have to do the extra capture and convert?

[Bisonte]

if you use the database timestamp functions to get a date, you have ever the servertime, not the local time. So what do you need ?

I made this with the Date() to avoid the converting... no problems so far

[RichAlgeni]

Basically I am storing status changes, and the associated date & time for ambulance units. The difference between the two times (the interval) is the amount of time a particular unit is in a particular status. That part is done. What I need to do now is to create a series of statistical reports using the intervals. It's much easier to manipulate the intervals when they are in internal PureBasic format, i.e. the number of seconds in the interval, rather than days, hours, minutes and seconds. I could store the interval as a 4 byte integer in the Sql row, thereby saving the steps needed to derive the internal interval. Has anyone run into this issue before and handled it this way? It's only 4 more bytes per row.

[graves]

Hi,
When you store the date on PB internal format, the main problem is you can't use it with external SQL clients, like WinSQL, for any purposes to testing, raw changes, fast reports...

Why not use ParseDate to store and FormatDate to display?.

[RichAlgeni]

Need to clarify a bit more, I apologize for that!

What my ultimate goal is, is to make reporting as easy as possible for users. To allow them to create their own reports in PostgreSql. So rather than having to subtract two fields, the result wil already be there for them.

Sorry about the confusion, as I'm still putting this all together!

Rich

[RichAlgeni]

Graves: I see what you mean!

I was hoping that a row modifier setup in Excel might handle what they need.

[RichAlgeni]

Just in case anyone is interested, I am going to err on the side of 'disk is cheap', and store more data than less.