Page 1 of 1
Storing PureBasic internal dates in SQL
Posted: Fri May 31, 2013 1:05 am
by 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?
Re: Storing PureBasic internal dates in SQL
Posted: Fri May 31, 2013 2:47 am
by 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()))
Re: Storing PureBasic internal dates in SQL
Posted: Fri May 31, 2013 3:11 am
by 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?
Re: Storing PureBasic internal dates in SQL
Posted: Fri May 31, 2013 10:32 am
by 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
Re: Storing PureBasic internal dates in SQL
Posted: Fri May 31, 2013 7:30 pm
by 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.
Re: Storing PureBasic internal dates in SQL
Posted: Sat Jun 01, 2013 9:00 am
by 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?.
Re: Storing PureBasic internal dates in SQL
Posted: Sat Jun 01, 2013 6:50 pm
by 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
Re: Storing PureBasic internal dates in SQL
Posted: Sat Jun 01, 2013 6:52 pm
by RichAlgeni
Graves: I see what you mean!
I was hoping that a row modifier setup in Excel might handle what they need.
Re: Storing PureBasic internal dates in SQL
Posted: Wed Jun 05, 2013 7:34 pm
by 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.