Changing the format of a date attribute

[Patric]

Hi everyone,

Firstly, thanks Alan for your help with my acct_start_time problem, that 
was exactly what I was after. The only problem that remains for me is 
getting the value into a different format so I can store it in my 
database table.


So I have the following setup currently:

share/dictionary.freeradius:

ATTRIBUTE   FreeRADIUS-Acct-Session-Start-Time  2   date


Then in my preacct section:

  update request {
  FreeRADIUS-Acct-Session-Start-Time = "%{expr: %l - 
%{%{Acct-Session-Time}:-0} - %{%{Acct-Delay-Time}:-0}}"

  }


Then I can use %{FreeRADIUS-Acct-Session-Start-Time} which is in the 
date format:


"Dec  8 2009 09:14:14 GMT".


The database field I will be writing into is a MySQL DateTime field, 
which is in the format "-MM-DD HH:ii:ss", eg:


"2009-12-08 11:27:34"


Is there any way for me to get my FreeRADIUS-Acct-Session-Start-Time 
attribute value into that date format?


Any advice would be very much appreciated.
Many thanks,
Patric
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: Changing the format of a date attribute

[Alan DeKok]

Patric wrote:
> Is there any way for me to get my FreeRADIUS-Acct-Session-Start-Time
> attribute value into that date format?

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_from-unixtime

  Alan DeKok.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: Changing the format of a date attribute

[Patric]

Alan DeKok wrote:

Patric wrote:

Is there any way for me to get my FreeRADIUS-Acct-Session-Start-Time
attribute value into that date format?


http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_from-unixtime
You sir are a genius :) It didnt even occur to me to do it in the SQL 
statement...


So now I have the following:

STR_TO_DATE('%{FreeRADIUS-Acct-Session-Start-Time}', '%M %d %Y %H:%i:%s'))

And that converts "Dec  8 2009 09:14:14 GMT" into "2009-12-08 09:14:14"

Thanks so much!
Patric
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: Changing the format of a date attribute

[Patric]

Hi again all :)

Patric wrote:

Alan DeKok wrote:

Patric wrote:

Is there any way for me to get my FreeRADIUS-Acct-Session-Start-Time
attribute value into that date format?


http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_from-unixtime 


So now I have the following:

STR_TO_DATE('%{FreeRADIUS-Acct-Session-Start-Time}', '%M %d %Y %H:%i:%s'))

And that converts "Dec  8 2009 09:14:14 GMT" into "2009-12-08 09:14:14"


I have a curious problem trying to format the date field in my MySQL 
statement as shown above.


In my sql/mysql/dialup.conf I have the following:

accounting_start_query_alt = "UPDATE ${acct_table_new} \
SET \
acct_start_time = STR_TO_DATE('%{FreeRADIUS-Acct-Session-Start-Time}', 
'%M %d %Y %H:%i:%s'), \

...


The problem with the above is that some of those formatting options ('%M 
%d %Y %H:%i:%s') are also defined as one-character variables, so instead 
of formatting the date with those options, its replacing each with the 
variable value, and when Im trying to end up with:


2009-12-08 09:14:14

instead Im ending up with:

2009-12-09 11:0126538264:AutoShapedVC


As you can see the minutes were replaced with the Calling Station ID and 
the seconds were replaced with the Connect-Info...


Is there any way for me to perhaps escape my format string, or some 
other work-around?


Many thanks
Patric
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: Changing the format of a date attribute

[Alan DeKok]

On 09-12-09 11:37 AM, Patric wrote:
> The problem with the above is that some of those formatting options ('%M
> %d %Y %H:%i:%s') are also defined as one-character variables, so instead
> of formatting the date with those options, its replacing each with the
> variable value, and when Im trying to end up with:

  Use %% to escape the %.  That should work.  e.g.

' ... %%M %%d %%Y %%H:%%i:%%s'

  Alan DeKok.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: Changing the format of a date attribute

[Patric]

Alan DeKok wrote:

On 09-12-09 11:37 AM, Patric wrote:
  

The problem with the above is that some of those formatting options ('%M
%d %Y %H:%i:%s') are also defined as one-character variables, so instead
of formatting the date with those options, its replacing each with the
variable value, and when Im trying to end up with:



  Use %% to escape the %.  That should work.  e.g.

' ... %%M %%d %%Y %%H:%%i:%%s'


Thanks Ill give that a go :)
Patric
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html