You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by l vic <lv...@gmail.com> on 2018/11/14 14:01:31 UTC
Getting a timestamp for today at midnight?
I have to retrieve "today's" records in ExecuteSQL, eg. with the query
using timestamp "ts" column in table where ts type is "epoch" time as long
integer :
select * from mytable where ts >= midnight-timestamp-value
Any idea how i can use "now()" function to get today's timestamp value at
midnight?
Thank you,
Re: Getting a timestamp for today at midnight?
Posted by l vic <lv...@gmail.com>.
That was it, brilliant.
2018/11/16 00:00:00
Thank you,
On Fri, Nov 16, 2018 at 4:22 PM Mark Payne <ma...@hotmail.com> wrote:
> Is that using the correct time zone? I.e., what if you print it out using:
>
> DateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
> sdf.setTimeZone(TimeZone.getTimeZone("UTC"));
> System.out.println(sdf.format(new Date(myTimeAsLong)));
>
> On Nov 16, 2018, at 4:16 PM, l vic <lv...@gmail.com> wrote:
>
> That returns timestamp corresponding to 19:00PM
> For example:
> *long* myTimeAsLong=1542326400000L; // from NiFi
> DateFormat sdf = *new* SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
> System.*out*.println(sdf.format(*new* Date(myTimeAsLong)));
>
> prints out 2018/11/15 19:00:00
>
> On Wed, Nov 14, 2018 at 9:21 AM Mark Payne <ma...@hotmail.com> wrote:
>
>> You should be able to do something like:
>>
>> ${now():divide( 86400000 ):multiply( 86400000)}
>>
>> I.e., use integer division to divide by number of milliseconds in a day,
>> which gives you
>> the number of days since epoch. Then multiply by 86,400,000 again to
>> convert from
>> days back to milliseconds. While it looks immediately like it would do
>> nothing, the thing
>> to keep in mind is that the divide() function performs and Integer
>> Division operation,
>> not a Decimal Division, so all decimals would be dropped, which
>> essentially results in
>> all hours/mins/seconds/milliseconds getting dropped.
>>
>> Thanks
>> -Mark
>>
>>
>> > On Nov 14, 2018, at 9:01 AM, l vic <lv...@gmail.com> wrote:
>> >
>> > I have to retrieve "today's" records in ExecuteSQL, eg. with the query
>> using timestamp "ts" column in table where ts type is "epoch" time as long
>> integer :
>> > select * from mytable where ts >= midnight-timestamp-value
>> > Any idea how i can use "now()" function to get today's timestamp value
>> at midnight?
>> > Thank you,
>>
>>
>
Re: Getting a timestamp for today at midnight?
Posted by Mark Payne <ma...@hotmail.com>.
Is that using the correct time zone? I.e., what if you print it out using:
DateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
sdf.setTimeZone(TimeZone.getTimeZone("UTC"));
System.out.println(sdf.format(new Date(myTimeAsLong)));
On Nov 16, 2018, at 4:16 PM, l vic <lv...@gmail.com>> wrote:
That returns timestamp corresponding to 19:00PM
For example:
long myTimeAsLong=1542326400000L; // from NiFi
DateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
System.out.println(sdf.format(new Date(myTimeAsLong)));
prints out 2018/11/15 19:00:00
On Wed, Nov 14, 2018 at 9:21 AM Mark Payne <ma...@hotmail.com>> wrote:
You should be able to do something like:
${now():divide( 86400000 ):multiply( 86400000)}
I.e., use integer division to divide by number of milliseconds in a day, which gives you
the number of days since epoch. Then multiply by 86,400,000 again to convert from
days back to milliseconds. While it looks immediately like it would do nothing, the thing
to keep in mind is that the divide() function performs and Integer Division operation,
not a Decimal Division, so all decimals would be dropped, which essentially results in
all hours/mins/seconds/milliseconds getting dropped.
Thanks
-Mark
> On Nov 14, 2018, at 9:01 AM, l vic <lv...@gmail.com>> wrote:
>
> I have to retrieve "today's" records in ExecuteSQL, eg. with the query using timestamp "ts" column in table where ts type is "epoch" time as long integer :
> select * from mytable where ts >= midnight-timestamp-value
> Any idea how i can use "now()" function to get today's timestamp value at midnight?
> Thank you,
Re: Getting a timestamp for today at midnight?
Posted by l vic <lv...@gmail.com>.
That returns timestamp corresponding to 19:00PM
For example:
*long* myTimeAsLong=1542326400000L; // from NiFi
DateFormat sdf = *new* SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
System.*out*.println(sdf.format(*new* Date(myTimeAsLong)));
prints out 2018/11/15 19:00:00
On Wed, Nov 14, 2018 at 9:21 AM Mark Payne <ma...@hotmail.com> wrote:
> You should be able to do something like:
>
> ${now():divide( 86400000 ):multiply( 86400000)}
>
> I.e., use integer division to divide by number of milliseconds in a day,
> which gives you
> the number of days since epoch. Then multiply by 86,400,000 again to
> convert from
> days back to milliseconds. While it looks immediately like it would do
> nothing, the thing
> to keep in mind is that the divide() function performs and Integer
> Division operation,
> not a Decimal Division, so all decimals would be dropped, which
> essentially results in
> all hours/mins/seconds/milliseconds getting dropped.
>
> Thanks
> -Mark
>
>
> > On Nov 14, 2018, at 9:01 AM, l vic <lv...@gmail.com> wrote:
> >
> > I have to retrieve "today's" records in ExecuteSQL, eg. with the query
> using timestamp "ts" column in table where ts type is "epoch" time as long
> integer :
> > select * from mytable where ts >= midnight-timestamp-value
> > Any idea how i can use "now()" function to get today's timestamp value
> at midnight?
> > Thank you,
>
>
Re: Getting a timestamp for today at midnight?
Posted by Mark Payne <ma...@hotmail.com>.
You should be able to do something like:
${now():divide( 86400000 ):multiply( 86400000)}
I.e., use integer division to divide by number of milliseconds in a day, which gives you
the number of days since epoch. Then multiply by 86,400,000 again to convert from
days back to milliseconds. While it looks immediately like it would do nothing, the thing
to keep in mind is that the divide() function performs and Integer Division operation,
not a Decimal Division, so all decimals would be dropped, which essentially results in
all hours/mins/seconds/milliseconds getting dropped.
Thanks
-Mark
> On Nov 14, 2018, at 9:01 AM, l vic <lv...@gmail.com> wrote:
>
> I have to retrieve "today's" records in ExecuteSQL, eg. with the query using timestamp "ts" column in table where ts type is "epoch" time as long integer :
> select * from mytable where ts >= midnight-timestamp-value
> Any idea how i can use "now()" function to get today's timestamp value at midnight?
> Thank you,