getting the current date/time

Hi,

Is there any way that I can (with SQL) retrieve the current date/time
that shows fractions of a second (milliseconds, microseconds, etc.).
If I execute:

select now()

It returns the current date/time, but only to the nearest second. I
would like to get a more precise measure of the current date/time and
if I could get it in the following format, that would be even better:

yyyy-mm-dd hh:mm:ss.ffff

Thanks in advance,
TD

in MySQL there is a DATE_FORMAT function which allows you to specify
the output of a date.

man this group is dead, is there a better group for these types of
questions?

-- clh


On Feb 17, 1:23 pm, donalmurt...@yahoo.co.uk wrote:
> Hi,
>
> Is there any way that I can (with SQL) retrieve the current date/time
> that shows fractions of a second (milliseconds, microseconds, etc.).
> If I execute:
>
> select now()
>
> It returns the current date/time, but only to the nearest second. I
> would like to get a more precise measure of the current date/time and
> if I could get it in the following format, that would be even better:
>
> yyyy-mm-dd hh:mm:ss.ffff
>
> Thanks in advance,
> TD

On Sun, 18 Feb 2007 18:21:32 +0100, <christopher@dailycrossword.com> wrote:

>
> in MySQL there is a DATE_FORMAT function which allows you to specify
> the output of a date.

But that still won't give you the _current_ microseconds...
--
Rik Wasmus

DATE_FORMAT(date,format)
....
%f Microseconds (000000..999999)

what do you mean by current? Oh, you are making a joke. Confusing,
since I just spent 20 minutes trying to figure out what you said I was
doing wrong.

-- clh


On Feb 18, 10:17 am, Rik <luiheidsgoe...@hotmail.com> wrote:
> On Sun, 18 Feb 2007 18:21:32 +0100, <christop...@dailycrossword.com> wrote:
>
> > in MySQL there is a DATE_FORMAT function which allows you to specify
> > the output of a date.
>
> But that still won't give you the _current_ microseconds...
> --
> Rik Wasmus

On Sun, 18 Feb 2007 19:59:16 +0100, <christopher@dailycrossword.com> wrote:
> On Feb 18, 10:17 am, Rik <luiheidsgoe...@hotmail.com> wrote:
>> On Sun, 18 Feb 2007 18:21:32 +0100, <christop...@dailycrossword.com>
>> wrote:
>>
>> > in MySQL there is a DATE_FORMAT function which allows you to specify
>> > the output of a date.
>>
>> But that still won't give you the _current_ microseconds...
>
> DATE_FORMAT(date,format)
> ...
> %f Microseconds (000000..999999)
>
> what do you mean by current? Oh, you are making a joke. Confusing,
> since I just spent 20 minutes trying to figure out what you said I was
> doing wrong.

I'm not joking, the question was not to get the microsconds form _a_ date
(which is hidiously easy with date_format), but a timestamp _with_ the
microseconds of the exact time it's called:

<donalmurtagh@yahoo.co.uk> wrote:
> Is there any way that I can (with SQL) retrieve the current date/time
> that shows fractions of a second (milliseconds, microseconds, etc.).
> If I execute:
>
> select now()
>
> It returns the current date/time, but only to the nearest second. I
> would like to get a more precise measure of the current date/time and
> if I could get it in the following format, that would be even better:
>
> yyyy-mm-dd hh:mm:ss.ffff

DATE_FORMAT(NOW(),'%f') will always result in 000000

So, in short, before you complain about the quality of a newsgroup don't
talk out of your ass.
--
Rik Wasmus

christopher@dailycrossword.com wrote:
> DATE_FORMAT(date,format)
> ...
> %f Microseconds (000000..999999)
>
> what do you mean by current? Oh, you are making a joke. Confusing,
> since I just spent 20 minutes trying to figure out what you said I was
> doing wrong.
>
> -- clh
>
>
> On Feb 18, 10:17 am, Rik <luiheidsgoe...@hotmail.com> wrote:
>> On Sun, 18 Feb 2007 18:21:32 +0100, <christop...@dailycrossword.com> wrote:
>>
>>> in MySQL there is a DATE_FORMAT function which allows you to specify
>>> the output of a date.
>> But that still won't give you the _current_ microseconds...
>> --
>> Rik Wasmus
>
>

Gee, what does the current date/time in microseconds have to do with
data stored in the database? I would think that would be something more
applicable to the language being used.

And I agree with Rik - if you want answers, don't complain about how
inactive this newsgroup is. It's a hell of a lot more active than some
of the newsgroups to which I belong. And most answers here are right on
target - unlike the more active groups which often contain lots of
off-topic posts and spam.

Quantity <> Quality!

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Thanks for the responses. Nobody has suggested a way of getting the
current time showing fractions of a second, so I guess it's just not
possible?

donalmurtagh@yahoo.co.uk wrote:
> Thanks for the responses. Nobody has suggested a way of getting the
> current time showing fractions of a second, so I guess it's just not
> possible?
>

This is a system function. I wouldn't expect a relational database to
provide the information. And if one did, I would expect it to be a
bonus, not a standard.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

>
> What exactly are you attempting to accomplish? Fractional seconds are
> seldom reliable anyway, since by the time one gets the result of a
> variable, it's usually not that milisecond anymore...
>

I want to detect whether the difference between the times on 2 boxes
exceeds a certain threshold. I know that I could use NTP in order to
synchronise the times on machines, but the purpose of this exercise is
not to synchronise times, but rather to detect when they're not
synchronised. I appreciate what you're saying about the delay between
executing the query and actually getting the result, but it's still
worth doing in this case.

donalmurtagh@yahoo.co.uk wrote:

>
>>
>> What exactly are you attempting to accomplish? Fractional seconds are
>> seldom reliable anyway, since by the time one gets the result of a
>> variable, it's usually not that milisecond anymore...
>>
>
> I want to detect whether the difference between the times on 2 boxes
> exceeds a certain threshold. I know that I could use NTP in order to
> synchronise the times on machines, but the purpose of this exercise is
> not to synchronise times, but rather to detect when they're not
> synchronised. I appreciate what you're saying about the delay between
> executing the query and actually getting the result, but it's still
> worth doing in this case.


So what has SQL got to do with it?

$ perl -MTime::HiRes=gettimeofday -e '$time=gettimeofday();print "$time\n"'
1171908671.04656


--
Brian Wakem
Email: http://homepage.ntlworld.com/b.wakem/myemail.png