Markie
Posts: 251
|
| Posted: 04/14/2009, 6:47 AM |
|
I have a log file which I want to store in MySQL, so I can make a CCS project in order to query the log file. The log file contains a date field, in this order: dd-MMM-yyyy (for example 15-May-2009). I want to convert this date field into a dd-MM-yyyy timestamp (for example: 2009-05-15) so I can write better queries in CCS. I wonder how to do this the most simple way.
Markie
_________________
The Netherlands, GMT+1
Tools: CCS 5.1, Windows 7, Navicat, Ultraedit
Local server: XAMPP with Apache, php and MySQL
Webserver: Windows 2008 IIS 7, php and MySQL |
 |
 |
datadoit
|
| Posted: 04/14/2009, 6:27 PM |
|
Markie wrote:
> I have a log file which I want to store in MySQL, so I can make a CCS project in
> order to query the log file. The log file contains a date field, in this order:
> dd-MMM-yyyy (for example 15-May-2009). I want to convert this date field into a
> dd-MM-yyyy timestamp (for example: 2009-05-15) so I can write better queries in
> CCS. I wonder how to do this the most simple way.
>
> Markie
> _________________
> The Netherlands, GMT+1
> Tools: CCS 4.1.00.027, Win XP, Navicat, PSPad
> Local server: XAMPP with Apache, php and MySQL
> Webserver: Ubuntu with Apache, php and MySQL
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>
"The log file contains a date field"
Explain this a little more. How exactly is the field defined? If it is
truly a DATE field (or DATETIME), then it is already a 'timestamp' as
you say. Write queries against it, display it, whatever you want.
Look deeper into the CCS Project Settings dialogs, along with connection
settings to learn more about how CCS will automate much of the date
handling for you (pending you define your database fields appropriately).
|
|
|
|
Markie
Posts: 251
|
| Posted: 04/15/2009, 4:13 AM |
|
Thanks for your advice. Now I have this date field as a timestamp in my MySQL dbase table and I can show it on my webpage, using this settings:
Name: date
Control Source Type: Database Column
Control Source: timestamp
Data Type: Date
Format: LongDate
Content: Text
DBFormat: yyyy-mm-dd HH:nn:ss
The Data Source settings (using VQB) for this grid are:
SELECT * from mytable WHERE date contains (like '%...%') s_date (type: Text, type: URL)
On my webpage, I have a search grid and I'm having a hard time to have it find dates. In the search grid I have a textfield with the name s_date, with this settings:
Name: s_date
Data Type: Date
Format: LongDate
Content: Text
DBFormat: yyyy-mm-dd HH:nn:ss
I must do something wrong because I don't get any hits when searching for a specific date
Any advice ?
_________________
The Netherlands, GMT+1
Tools: CCS 5.1, Windows 7, Navicat, Ultraedit
Local server: XAMPP with Apache, php and MySQL
Webserver: Windows 2008 IIS 7, php and MySQL |
|
|
|
datadoit
|
| Posted: 04/15/2009, 7:19 AM |
|
Markie wrote:
> Name: s_date
> Data Type: Date
> Format: LongDate
> Content: Text
> DBFormat: yyyy-mm-dd HH:nn:ss
>
Recognize that in your VBQ's WHERE parameters settings, there are -TWO-
areas of concern: What the database has and what your URL has. CCS will
do the legwork for converting a textual representation of the date (the
URL) into a text format the database will recognize. See CCParseDate()
and CCFormatDate() functions for details on how this works.
In the VBQ's WHERE parameter, you should have something similar to:
Condition Type: Parameter
Field Condition Parameter
Name: date whatever s_date
Type: Date Type: URL
Format: yyyy-mm-dd HH:nn:ss Format: mm/dd/yyyy
Also note that you don't want to try to pass a LongDate in the URL.
There could be unexpected results on some web servers because of the commas.
|
|
|
|
Markie
Posts: 251
|
| Posted: 04/18/2009, 2:23 AM |
|
darn! I can't get it to work, it's essential to me that it will work somehow, so I hope you can help me find the solution.
I have a database table with (a.o.) this setting:
Field: Created
Type: datetime (example: 2008-01-01 15:36:08)
I have a CCS page with a grid, which has this setting for the field Created:
Data Type: date
Format: ShortDate
Content: Text
DBformat: yyyy-mm-dd HH:nn:ss
In the VBQ's settings of this grid, I have this:
SELECT * from mytable WHERE Created equals (=) s_Created
Type: Date | Type: URL
Format: yyyy-mm-dd HH:nn:ss | Format: ShortDate
Use default value if parameter is empty
On the same page as the grid I have a search grid with a.o. a field for s_Created, with this settings:
Control source type: Database column
Data type: date
Format: ShortDate
DBformat: yyyy-mm-dd HH:nn:ss
I know there are a lot of records in my database table for, let's say, April 10 2009. Now, when I search for this date, I get no results.
What do I do wrong ?
_________________
The Netherlands, GMT+1
Tools: CCS 5.1, Windows 7, Navicat, Ultraedit
Local server: XAMPP with Apache, php and MySQL
Webserver: Windows 2008 IIS 7, php and MySQL |
|
|
|
Markie
Posts: 251
|
| Posted: 04/21/2009, 1:48 AM |
|
Okay, I get the point. It's working now, thanks datadoit !
_________________
The Netherlands, GMT+1
Tools: CCS 5.1, Windows 7, Navicat, Ultraedit
Local server: XAMPP with Apache, php and MySQL
Webserver: Windows 2008 IIS 7, php and MySQL |
|
|
|
datadoit
|
| Posted: 04/21/2009, 9:03 AM |
|
Markie, it depends on how you're searching that DATETIME field. If
you're searching for a date such as 2009-04-21, without specifying a
time, then the search will look for 2009-04-21 00:00:00. If you don't
have that exact timestamp in your database, it'll not find the record.
You can't pull records for today on a DATETIME field using just the date
portion (s_date = 2009-04-21). It won't work unless you have the above
timestamp. You MUST use s_date >= 2009-04-21 to get today's records.
If you want yesterday's records, you must use (s_date >= 2009-04-20 AND
s_date <= 2009-04-21), or search from midnight yesterday morning to
midnight this morning.
HTH.
|
|
|
|
Markie
Posts: 251
|
| Posted: 04/21/2009, 1:25 PM |
|
Hi datadoit, yes, that's exactly what was the case.
Thanks for your help.
_________________
The Netherlands, GMT+1
Tools: CCS 5.1, Windows 7, Navicat, Ultraedit
Local server: XAMPP with Apache, php and MySQL
Webserver: Windows 2008 IIS 7, php and MySQL |
|
|
|
|
