Minimum and Maximum date in Elasticsearch


(vijuitech) #1

Hi,
Is there any alternative in elasticsearch for the MySqlQuery

SELECT MIN(Date) AS MINDATE, MAX(Date) AS MAXDATE, HOUR(Date) FROM
TableName;

How can I get the minimum or maximum date in elasticsearch?

Cheers

George


(Eric Jain) #2

On Apr 5, 1:59 am, George Viju vijuit...@gmail.com wrote:

How can I get the minimum or maximum date in elasticsearch?

With a statistical facet:

http://www.elasticsearch.org/guide/reference/api/search/facets/statistical-facet.html


(vijuitech) #3

Thank You Eric Jain

Cheers!!

George

On Apr 5, 9:05 pm, Eric Jain eric.j...@gmail.com wrote:

On Apr 5, 1:59 am, George Viju vijuit...@gmail.com wrote:

How can I get the minimum or maximum date in elasticsearch?

With a statistical facet:

http://www.elasticsearch.org/guide/reference/api/search/facets/statis...


(vijuitech) #4

Hi,
I tried the statistical facet for getting the minimum and maximum
date. I got the output as

createddate" : {
"_type" : "statistical",
"count" : 158,
"total" : 2.10720682311E14,
"min" : 1.3332708E12,
"max" : 1.3340484E12,
"mean" : 1.3336752045E12,
"sum_of_squares" : 2.8103295903276077E26,
"variance" : 6.30332803714792E16,
"std_deviation" : 2.510642952940127E8
}

How can I get the actual minimum and maximum date from here?

(For example the min or max date may be this format:

"createddate" : "2012-04-10T09:00:00.000Z",)

cheers!

George

On Apr 7, 11:10 am, George Viju vijuit...@gmail.com wrote:

Thank You Eric Jain

Cheers!!

George

On Apr 5, 9:05 pm, Eric Jain eric.j...@gmail.com wrote:

On Apr 5, 1:59 am,GeorgeVijuvijuit...@gmail.com wrote:

How can I get the minimum or maximum date in elasticsearch?

With a statistical facet:

http://www.elasticsearch.org/guide/reference/api/search/facets/statis...


(Eric Jain) #5

On Tue, Apr 10, 2012 at 00:45, George Viju vijuitech@gmail.com wrote:

 "min" : 1.3332708E12,

Looks like you'll need to convert the float to a long to a date yourself...

-> 2012-04-01T09:00:00.000Z


(vijuitech) #6

Thank you Eric Jain. I converted the min value 1.3332708E12 to
date returns 2012-04-01 14:30:06, But the actual minimum date is
2012-04-01 09:00:00,
and max value 1.3340484E12 to date returns 2012-04-10 14:30:56,
But the actual maximum date is 2012-04-10 09:00:00. There is a
difference in time in both date.

Cheers!

George

On Apr 10, 1:40 pm, Eric Jain eric.j...@gmail.com wrote:

On Tue, Apr 10, 2012 at 00:45, George Viju vijuit...@gmail.com wrote:

 "min" : 1.3332708E12,

Looks like you'll need to convert the float to a long to a date yourself...

-> 2012-04-01T09:00:00.000Z


(Eric Jain) #7

On Tue, Apr 10, 2012 at 02:41, George Viju vijuitech@gmail.com wrote:

Thank you Eric Jain. I converted the min value 1.3332708E12 to
date returns 2012-04-01 14:30:06, But the actual minimum date is
2012-04-01 09:00:00,
and max value 1.3340484E12 to date returns 2012-04-10 14:30:56,
But the actual maximum date is 2012-04-10 09:00:00. There is a
difference in time in both date.

Can you show the code you use for the conversion?

new DateTime((long) 1.3332708E12, DateTimeZone.UTC);
-> 2012-04-01T09:00:00.000Z


(vijuitech) #8

Thank you very much Eric Jain. I have used Date method instead of
joda time in the code. Now it is working fine.

Cheers!

George

On Apr 10, 2:54 pm, Eric Jain eric.j...@gmail.com wrote:

On Tue, Apr 10, 2012 at 02:41, George Viju vijuit...@gmail.com wrote:

Thank you Eric Jain. I converted the min value 1.3332708E12 to
date returns 2012-04-01 14:30:06, But the actual minimum date is
2012-04-01 09:00:00,
and max value 1.3340484E12 to date returns 2012-04-10 14:30:56,
But the actual maximum date is 2012-04-10 09:00:00. There is a
difference in time in both date.

Can you show the code you use for the conversion?

new DateTime((long) 1.3332708E12, DateTimeZone.UTC);
-> 2012-04-01T09:00:00.000Z


(Shay Banon) #9

Yea, the date returned in this case is milliseconds since the epoch (UTC).

On Tue, Apr 10, 2012 at 1:32 PM, George Viju vijuitech@gmail.com wrote:

Thank you very much Eric Jain. I have used Date method instead of
joda time in the code. Now it is working fine.

Cheers!

George

On Apr 10, 2:54 pm, Eric Jain eric.j...@gmail.com wrote:

On Tue, Apr 10, 2012 at 02:41, George Viju vijuit...@gmail.com wrote:

Thank you Eric Jain. I converted the min value 1.3332708E12 to
date returns 2012-04-01 14:30:06, But the actual minimum date is
2012-04-01 09:00:00,
and max value 1.3340484E12 to date returns 2012-04-10 14:30:56,
But the actual maximum date is 2012-04-10 09:00:00. There is a
difference in time in both date.

Can you show the code you use for the conversion?

new DateTime((long) 1.3332708E12, DateTimeZone.UTC);
-> 2012-04-01T09:00:00.000Z


(vijuitech) #10

Thank You Shay.

I got the minimum and maximum date from the statistical facet and the
output is

"createddatecount" : {
"_type" : "statistical",
"count" : 17,
"total" : 2.2680306797E13,
"min" : 1.3341348E12,
"max" : 1.334144297E12,
"mean" : 1.3341356939411765E12,
"sum_of_squares" : 3.025860684752347E25,
"variance" : 6.417439075629177E12,
"std_deviation" : 2533266.483343033
},

By using the dateHistogramFacet I get the post count of each date

"createddaterange" : {
  "_type" : "date_histogram",
  "entries" : [ {
    "time" : 1334134800000,
    "count" : 15
  }, {
    "time" : 1334138400000,
    "count" : 1
  }, {
    "time" : 1334142000000,
    "count" : 1
  } ]
}

How can I get the minimum and maximum date and the count of each
date in a single Facet?

Cheers!!

George

On Apr 11, 4:33 pm, Shay Banon kim...@gmail.com wrote:

Yea, the date returned in this case is milliseconds since the epoch (UTC).

On Tue, Apr 10, 2012 at 1:32 PM, George Viju vijuit...@gmail.com wrote:

Thank you very much Eric Jain. I have used Date method instead of
joda time in the code. Now it is working fine.

Cheers!

George

On Apr 10, 2:54 pm, Eric Jain eric.j...@gmail.com wrote:

On Tue, Apr 10, 2012 at 02:41, George Viju vijuit...@gmail.com wrote:

Thank you Eric Jain. I converted the min value 1.3332708E12 to
date returns 2012-04-01 14:30:06, But the actual minimum date is
2012-04-01 09:00:00,
and max value 1.3340484E12 to date returns 2012-04-10 14:30:56,
But the actual maximum date is 2012-04-10 09:00:00. There is a
difference in time in both date.

Can you show the code you use for the conversion?

new DateTime((long) 1.3332708E12, DateTimeZone.UTC);
-> 2012-04-01T09:00:00.000Z


(Shay Banon) #11

You mean each date in the date histogram? You need to add a value field
(can be the same date field), in which case, stats will be computed on it.

On Wed, Apr 11, 2012 at 4:51 PM, George Viju vijuitech@gmail.com wrote:

Thank You Shay.

I got the minimum and maximum date from the statistical facet and the
output is

"createddatecount" : {
"_type" : "statistical",
"count" : 17,
"total" : 2.2680306797E13,
"min" : 1.3341348E12,
"max" : 1.334144297E12,
"mean" : 1.3341356939411765E12,
"sum_of_squares" : 3.025860684752347E25,
"variance" : 6.417439075629177E12,
"std_deviation" : 2533266.483343033
},

By using the dateHistogramFacet I get the post count of each date

"createddaterange" : {
"_type" : "date_histogram",
"entries" : [ {
"time" : 1334134800000,
"count" : 15
}, {
"time" : 1334138400000,
"count" : 1
}, {
"time" : 1334142000000,
"count" : 1
} ]
}

How can I get the minimum and maximum date and the count of each
date in a single Facet?

Cheers!!

George

On Apr 11, 4:33 pm, Shay Banon kim...@gmail.com wrote:

Yea, the date returned in this case is milliseconds since the epoch
(UTC).

On Tue, Apr 10, 2012 at 1:32 PM, George Viju vijuit...@gmail.com
wrote:

Thank you very much Eric Jain. I have used Date method instead of
joda time in the code. Now it is working fine.

Cheers!

George

On Apr 10, 2:54 pm, Eric Jain eric.j...@gmail.com wrote:

On Tue, Apr 10, 2012 at 02:41, George Viju vijuit...@gmail.com
wrote:

Thank you Eric Jain. I converted the min value 1.3332708E12 to
date returns 2012-04-01 14:30:06, But the actual minimum date is
2012-04-01 09:00:00,
and max value 1.3340484E12 to date returns 2012-04-10 14:30:56,
But the actual maximum date is 2012-04-10 09:00:00. There is a
difference in time in both date.

Can you show the code you use for the conversion?

new DateTime((long) 1.3332708E12, DateTimeZone.UTC);
-> 2012-04-01T09:00:00.000Z


(vijuitech) #12

Thank you Shay.

Yes, for each date or time in date Histogram I need the statistical
data of min and max values.

" You need to add a value field
(can be the same date field), in which case, stats will be computed on
it."

Could you please clarify this?

Thanks in advance!

Cheers!

George

On Apr 11, 11:46 pm, Shay Banon kim...@gmail.com wrote:

You mean each date in the date histogram? You need to add a value field
(can be the same date field), in which case, stats will be computed on it.

On Wed, Apr 11, 2012 at 4:51 PM, George Viju vijuit...@gmail.com wrote:

Thank You Shay.

I got the minimum and maximum date from the statistical facet and the
output is

"createddatecount" : {
"_type" : "statistical",
"count" : 17,
"total" : 2.2680306797E13,
"min" : 1.3341348E12,
"max" : 1.334144297E12,
"mean" : 1.3341356939411765E12,
"sum_of_squares" : 3.025860684752347E25,
"variance" : 6.417439075629177E12,
"std_deviation" : 2533266.483343033
},

By using the dateHistogramFacet I get the post count of each date

"createddaterange" : {
"_type" : "date_histogram",
"entries" : [ {
"time" : 1334134800000,
"count" : 15
}, {
"time" : 1334138400000,
"count" : 1
}, {
"time" : 1334142000000,
"count" : 1
} ]
}

How can I get the minimum and maximum date and the count of each
date in a single Facet?

Cheers!!

George

On Apr 11, 4:33 pm, Shay Banon kim...@gmail.com wrote:

Yea, the date returned in this case is milliseconds since the epoch
(UTC).

On Tue, Apr 10, 2012 at 1:32 PM, George Viju vijuit...@gmail.com
wrote:

Thank you very much Eric Jain. I have used Date method instead of
joda time in the code. Now it is working fine.

Cheers!

George

On Apr 10, 2:54 pm, Eric Jain eric.j...@gmail.com wrote:

On Tue, Apr 10, 2012 at 02:41, George Viju vijuit...@gmail.com
wrote:

Thank you Eric Jain. I converted the min value 1.3332708E12 to
date returns 2012-04-01 14:30:06, But the actual minimum date is
2012-04-01 09:00:00,
and max value 1.3340484E12 to date returns 2012-04-10 14:30:56,
But the actual maximum date is 2012-04-10 09:00:00. There is a
difference in time in both date.

Can you show the code you use for the conversion?

new DateTime((long) 1.3332708E12, DateTimeZone.UTC);
-> 2012-04-01T09:00:00.000Z


(Eric Jain) #13

On Wed, Apr 11, 2012 at 23:26, George Viju vijuitech@gmail.com wrote:

" You need to add a value field
(can be the same date field), in which case, stats will be computed on
it."

Could you please clarify this?

See http://www.elasticsearch.org/guide/reference/api/search/facets/date-histogram-facet.html,
"Value Field".


(vijuitech) #14

ok. Thank you Eric Jain

Cheers!

George

On Apr 12, 11:43 am, Eric Jain eric.j...@gmail.com wrote:

On Wed, Apr 11, 2012 at 23:26, George Viju vijuit...@gmail.com wrote:

" You need to add a value field
(can be the same date field), in which case, stats will be computed on
it."

Could you please clarify this?

Seehttp://www.elasticsearch.org/guide/reference/api/search/facets/date-h...,
"Value Field".


(vijuitech) #15

Hi,
I can get the data for year, month, week, day, hour, minute Date
Histogram facet. Is there any method to get the quarter(3 months)
period data in date Histogram Facet?

Cheers!!

George

On Apr 12, 12:39 pm, George Viju vijuit...@gmail.com wrote:

ok. Thank you Eric Jain

Cheers!

George

On Apr 12, 11:43 am, Eric Jain eric.j...@gmail.com wrote:

On Wed, Apr 11, 2012 at 23:26, George Viju vijuit...@gmail.com wrote:

" You need to add a value field
(can be the same date field), in which case, stats will be computed on
it."

Could you please clarify this?

Seehttp://www.elasticsearch.org/guide/reference/api/search/facets/date-h...,
"Value Field".


(Eric Jain) #16

On Thu, Apr 12, 2012 at 05:35, George Viju vijuitech@gmail.com wrote:

I can get the data for year, month, week, day, hour, minute Date
Histogram facet. Is there any method to get the quarter(3 months)
period data in date Histogram Facet?

Get the months and add them up?


(Shay Banon) #17

Getting the months and adding them up is your best option, there isn't a "3
months" option.

On Thu, Apr 12, 2012 at 9:26 PM, Eric Jain eric.jain@gmail.com wrote:

On Thu, Apr 12, 2012 at 05:35, George Viju vijuitech@gmail.com wrote:

I can get the data for year, month, week, day, hour, minute Date
Histogram facet. Is there any method to get the quarter(3 months)
period data in date Histogram Facet?

Get the months and add them up?


(vijuitech) #18

Thank You.. I tried the Date Histogram Facet for getting the weeks.
Here I am getting the Week starting from Monday to Sunday as one Week.
Is there any method to get the Week that should start from Sunday to
Saturday as one Week.

For Example, If I am taking the date from April 1 to April 13, The
Week count Should be 2, but I am getting 3 weeks because It is taking
the week start from Monday to Sunday.

"daterange" : {
"_type" : "date_histogram",
"entries" : [ {
"time" : 1332720000000,
"count" : 15,
"min" : 1.3332708E12,
"max" : 1.3332708E12,
"total" : 1.9999062E13,
"total_count" : 15,
"mean" : 1.3332708E12
}, {
"time" : 1333324800000,
"count" : 105,
"min" : 1.3333572E12,
"max" : 1.3338756E12,
"total" : 1.40029722E14,
"total_count" : 105,
"mean" : 1.3336164E12
}, {
"time" : 1333929600000,
"count" : 85,
"min" : 1.333962E12,
"max" : 1.3343076E12,
"total" : 1.13400137108E14,
"total_count" : 85,
"mean" : 1.3341192600941177E12
} ]
}

Is there any method to get the Week that should start from Sunday to
Saturday as one Week?

Cheers!

George

On Apr 13, 5:29 pm, Shay Banon kim...@gmail.com wrote:

Getting the months and adding them up is your best option, there isn't a "3
months" option.

On Thu, Apr 12, 2012 at 9:26 PM, Eric Jain eric.j...@gmail.com wrote:

On Thu, Apr 12, 2012 at 05:35, George Viju vijuit...@gmail.com wrote:

I can get the data for year, month, week, day, hour, minute Date
Histogram facet. Is there any method to get the quarter(3 months)
period data in date Histogram Facet?

Get the months and add them up?


(vijuitech) #19

Hi,
How to use TimeZoneRounding.Builder method postOffset(113232)
here to make it start from Sunday to Saturday as referred in

Cheers!

George

On Apr 16, 8:56 am, George Viju vijuit...@gmail.com wrote:

Thank You.. I tried the Date Histogram Facet for getting the weeks.
Here I am getting the Week starting from Monday to Sunday as one Week.
Is there any method to get the Week that should start from Sunday to
Saturday as one Week.

For Example, If I am taking the date from April 1 to April 13, The
Week count Should be 2, but I am getting 3 weeks because It is taking
the week start from Monday to Sunday.

"daterange" : {
"_type" : "date_histogram",
"entries" : [ {
"time" : 1332720000000,
"count" : 15,
"min" : 1.3332708E12,
"max" : 1.3332708E12,
"total" : 1.9999062E13,
"total_count" : 15,
"mean" : 1.3332708E12
}, {
"time" : 1333324800000,
"count" : 105,
"min" : 1.3333572E12,
"max" : 1.3338756E12,
"total" : 1.40029722E14,
"total_count" : 105,
"mean" : 1.3336164E12
}, {
"time" : 1333929600000,
"count" : 85,
"min" : 1.333962E12,
"max" : 1.3343076E12,
"total" : 1.13400137108E14,
"total_count" : 85,
"mean" : 1.3341192600941177E12
} ]
}

Is there any method to get the Week that should start from Sunday to
Saturday as one Week?

Cheers!

George

On Apr 13, 5:29 pm, Shay Banon kim...@gmail.com wrote:

Getting the months and adding them up is your best option, there isn't a "3
months" option.

On Thu, Apr 12, 2012 at 9:26 PM, Eric Jain eric.j...@gmail.com wrote:

On Thu, Apr 12, 2012 at 05:35,GeorgeVijuvijuit...@gmail.com wrote:

I can get the data for year, month, week, day, hour, minute Date
Histogram facet. Is there any method to get the quarter(3 months)
period data in date Histogram Facet?

Get the months and add them up?


(vijuitech) #20

Hi,
Is there any method to make the week to start from Sunday and
ends at Saturday? How to use postOffset() method with DateHistogram
Facet?

Cheers!

George

On Apr 17, 12:30 pm, George Viju vijuit...@gmail.com wrote:

Hi,
How to use TimeZoneRounding.Builder method postOffset(113232)
here to make it start from Sunday to Saturday as referred in

https://github.com/elasticsearch/elasticsearch/issues/1599

Cheers!

George

On Apr 16, 8:56 am, George Viju vijuit...@gmail.com wrote:

Thank You.. I tried the Date Histogram Facet for getting the weeks.
Here I am getting the Week starting from Monday to Sunday as one Week.
Is there any method to get the Week that should start from Sunday to
Saturday as one Week.

For Example, If I am taking the date from April 1 to April 13, The
Week count Should be 2, but I am getting 3 weeks because It is taking
the week start from Monday to Sunday.

"daterange" : {
"_type" : "date_histogram",
"entries" : [ {
"time" : 1332720000000,
"count" : 15,
"min" : 1.3332708E12,
"max" : 1.3332708E12,
"total" : 1.9999062E13,
"total_count" : 15,
"mean" : 1.3332708E12
}, {
"time" : 1333324800000,
"count" : 105,
"min" : 1.3333572E12,
"max" : 1.3338756E12,
"total" : 1.40029722E14,
"total_count" : 105,
"mean" : 1.3336164E12
}, {
"time" : 1333929600000,
"count" : 85,
"min" : 1.333962E12,
"max" : 1.3343076E12,
"total" : 1.13400137108E14,
"total_count" : 85,
"mean" : 1.3341192600941177E12
} ]
}

Is there any method to get the Week that should start from Sunday to
Saturday as one Week?

Cheers!

George

On Apr 13, 5:29 pm, Shay Banon kim...@gmail.com wrote:

Getting the months and adding them up is your best option, there isn't a "3
months" option.

On Thu, Apr 12, 2012 at 9:26 PM, Eric Jain eric.j...@gmail.com wrote:

On Thu, Apr 12, 2012 at 05:35,GeorgeVijuvijuit...@gmail.com wrote:

I can get the data for year, month, week, day, hour, minute Date
Histogram facet. Is there any method to get the quarter(3 months)
period data in date Histogram Facet?

Get the months and add them up?