skaits peec datuma

[andre]

man ir tabula ar rowu datetime

datums

2003-12-12 14:14:14

2003-12-12 14:14:13

2003-12-12 14:14:12

2003-12-13 14:14:14

2003-12-13 14:14:14

2003-12-13 15:14:14

2003-12-14 14:14:14

2003-12-18 14:14:14

2003-12-18 13:13:12

 

kaa es varu izselekteet no tabulas skaitu, cik kuraa datumaa ir bijushi ieraksti

rezultatam jaabut

2003-12-12 | 3

2003-12-13 | 3

2003-12-14 | 1

2003-12-18 | 2

[sorehead]

Protams var but ir cits risinajums, bet es dariitu sekojosi:

 

TABULA:

-------------------------------------------------

mysql> desc laix;

+--------+----------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+----------+------+-----+---------+-------+

| datums | datetime | YES | | NULL | |

+--------+----------+------+-----+---------+-------+

1 row in set (0.09 sec)

 

DATI:

-------------------------------------------------

mysql> select * from laix;

+---------------------+

| datums |

+---------------------+

| 2003-12-12 14:14:14 |

| 2003-12-12 14:14:13 |

| 2003-12-12 14:14:12 |

| 2003-12-13 14:14:14 |

| 2003-12-13 14:14:14 |

| 2003-12-13 15:14:14 |

| 2003-12-14 14:14:14 |

| 2003-12-18 14:14:14 |

| 2003-12-18 13:13:12 |

+---------------------+

9 rows in set (0.00 sec)

 

VAICAJUMS:

-------------------------------------------------

 

mysql> SELECT DATE_FORMAT(datums,'%Y-%e-%d') as laix, count(*) as amount FROM laix group by laix;

+------------+--------+

| laix | amount |

+------------+--------+

| 2003-12-12 | 3 |

| 2003-13-13 | 3 |

| 2003-14-14 | 1 |

| 2003-18-18 | 2 |

+------------+--------+

4 rows in set (0.01 sec)

[sorehead]

neliels buxfix :)

 

mysql> SELECT DATE_FORMAT(datums,'%Y-%m-%d') as laix, count(*) as amount FROM laix group by laix;

+------------+--------+

| laix | amount |

+------------+--------+

| 2003-12-12 | 3 |

| 2003-12-13 | 3 |

| 2003-12-14 | 1 |

| 2003-12-18 | 2 |

+------------+--------+

4 rows in set (0.01 sec)

[andre]

thx...

[Venom]

/me ir parociigi glabaat laiku ieksh Unix timestampiem. priekshrociibas:

order by straadaa uzreiz. Viegli atlsaiit postus peec kaut kaada laika - SELECT WHERE time>UNIX_TIMESTAMP('1997-10-04 22:23:00'); Pie tam ir arii FROM_UNIXTIME, ar ko var formateet datumus no tabulas peec veelmes FROM_UNIXTIME(unix_timestamp,'%Y %D %M %h:%i:%s %x');

[Roze]

Tikpat parociigi ir glabaat datumu ieksh DATETIME jo tam var piemerot visas standarta string funkcijas

 

Piemeeram:

 

.. WHERE datums LIKE '2003-10-%' utt utt

 

Ja runaajam par datumu formateeshanu tad ir funkcija DATE_FORMAT ar kuru tad var salikt analogi date() funkcijai php kaadu datumu formaatu galugalaa gribam..

 

Bez tam datetime ir human readable tapeec vienkaarshaka caurskatiit kaut kaadas tabulas ko ar timestamp nevaram bez konverteeshanas..

 

Un kapeec tad order by nestradaatu ar DATETIME? Vieniigi papildus ja datumu formateejam ar DATE_FORMAT tad jaapielieto arii funkcija CAST (AS DATE, DATETIME).. Preteeji galarezultaats ir strings kuram ir savadaakas iipatniibas.

[Venom]

Nu nezinu gan, man vieglaak peec tam taisiit piem. gmdate('formaats',$stamps_no_baazes) un katru reizi nevajag formateet tieshi querijaa

[eergl]

Pieļauju iespēju, ka querijā funkciju izpilde aizņem vairāk mums tik dārgā laika... :rolleyes:

[Venom]

kveerijaa vareetu izpildiities pietiekami aatri, ilgaak buutu jaaraksta sql parametri, kuriem es pilno sintaksi neatceros. bet ieksh php man pilniigi pietiek ar 'y/m/d H:i:s'.

[jb4]

Ja uzdevums ir tik vienkāršs, tad var arī to vienkārši risināt un par datumiem nedomāt:

 

SELECT SUBSTRING(datums,0,10), COUNT(*) FROM tabula GROUP BY SUBSTRING(datums,0,10), ORDER BY datums ASC

 

Lai lietas noritētu ātrak, var uzlikt INDEX uz pirmajiem 10 attiecīgā lauka characteriem. Šis domāts MySQL vecajām versijām, kur ne pārāk forši ar datumu funkcijām iet.