Hi,
I have a field to record the current time stamp while inserting each
record.
And it reads the Mysql server time while inserting well...

But, i need to record my local time ( Dubai Time)
and server is in US

( we can do it through PHP by setting the Time zone while inserting,
but how can the Mysql do the same job for me?)

Please help

Regards,
Anees

On 18 Mar, 07:41, Anees <muhd.an...@gmail.com> wrote:
> Hi,
> I have a field to record the current time stamp while inserting each
> record.
> And it reads the Mysql server time while inserting well...
>
> But, i need to record my local time ( Dubai Time)
> and server is in US
>
> ( we can do it through PHP by setting the Time zone while inserting,
> but how can the Mysql do the same job for me?)
>
> Please help
>
> Regards,
> Anees

Store using UTC_TIMESTAMP and convert to local time when accessing.

> Store using UTC_TIMESTAMP and convert to local time when accessing.

The field description given, when i created the table was

cur_time timestamp CURRENT_TIMESTAMP

So u r telling instead of inserting the current time automatically, i
need to pass the value after calling UTC_TIMESTAMP from PHP?

On 18 Mar, 10:36, Anees <muhd.an...@gmail.com> wrote:
> > Store using UTC_TIMESTAMP and convert to local time when accessing.
>
> The field description given, when i created the table was
>
> cur_time timestamp CURRENT_TIMESTAMP
>
> So u r telling instead of inserting the current time automatically, i
> need to pass the value after calling UTC_TIMESTAMP from PHP?

No, UTC_TIMESTAMP() is a MySQL function, not a php function. I am
telling you to use this to insert the current time automatically.

On Tue, 18 Mar 2008 04:11:34 -0700 (PDT), Captain Paralytic wrote:
> On 18 Mar, 10:36, Anees <muhd.an...@gmail.com> wrote:
>> > Store using UTC_TIMESTAMP and convert to local time when accessing.
>>
>> The field description given, when i created the table was
>>
>> cur_time timestamp CURRENT_TIMESTAMP
>>
>> So u r telling instead of inserting the current time automatically, i
>> need to pass the value after calling UTC_TIMESTAMP from PHP?
>
> No, UTC_TIMESTAMP() is a MySQL function, not a php function. I am
> telling you to use this to insert the current time automatically.

Unfortunately for Anees, this will not get exactly what is wanted, which
is local time in Dubai. It will, however, remove whatever local time
bais exists. It does also complicate the coding somewhat in that the
UTC_TIMESTAMP doesn't seem usable as a DEFAULT value at least on 5.0,

create table `test`.`utc_test` (
`rec_seq` int NOT NULL ,
`val` varchar NULL ,
`last_update` timestamp DEFAULT UTC_TIMESTAMP ,
`created_at` timestamp DEFAULT UTC_TIMESTAMP ,
PRIMARY KEY ( `rec_seq` )
)

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL ,
`last_update` timestamp DEFAULT UTC_TIMESTAMP ,
`created_at' at line 3
(0 ms taken)

and the auto-update function may be problematic.


--
Remember, a 12'x12'x18" raised floor can hold over a thousand gallons of
blood before it starts to seep up through the cracks.
-- Roger Burton West in the Monastery

> No, UTC_TIMESTAMP() is a MySQL function, not a php function. I am
> telling you to use this to insert the current time automatically.

Can u Please give the sql query eg. to edit a field that using the
same function
when i tried
ALTER TABLE `product` ADD `current_time` TIMESTAMP NOT NULL DEFAULT
'UTC_TIMESTAMP()'

i got an error near the UTC_TIMESTAMP function

On 18 Mar, 12:40, "Peter H. Coffin" <hell...@ninehells.com> wrote:
> On Tue, 18 Mar 2008 04:11:34 -0700 (PDT), Captain Paralytic wrote:
> > On 18 Mar, 10:36, Anees <muhd.an...@gmail.com> wrote:
> >> > Store using UTC_TIMESTAMP and convert to local time when accessing.
>
> >> The field description given, when i created the table was
>
> >> cur_time timestamp CURRENT_TIMESTAMP
>
> >> So u r telling instead of inserting the current time automatically, i
> >> need to pass the value after calling UTC_TIMESTAMP from PHP?
>
> > No, UTC_TIMESTAMP() is a MySQL function, not a php function. I am
> > telling you to use this to insert the current time automatically.
>
> Unfortunately for Anees, this will not get exactly what is wanted, which
> is local time in Dubai.
This part out of context will not, but I believe that my actual
solution posted in my original reply will supply what was requested.

> It will, however, remove whatever local time
> bais exists. It does also complicate the coding somewhat
It will change the coding certainly. But I do not think it will become
particularly complex. One just needs to follow the correct syntax for
one's needs.

> in that the
> UTC_TIMESTAMP doesn't seem usable as a DEFAULT value at least on 5.0,
>
> create table `test`.`utc_test` (
> `rec_seq` int NOT NULL ,
> `val` varchar NULL ,
> `last_update` timestamp DEFAULT UTC_TIMESTAMP ,
> `created_at` timestamp DEFAULT UTC_TIMESTAMP ,
> PRIMARY KEY ( `rec_seq` )
> )
>
> Error Code : 1064
> You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL ,
> `last_update` timestamp DEFAULT UTC_TIMESTAMP ,
> `created_at' at line 3
> (0 ms taken)
This error refers to you having missed the field size from the
VARCHAR, it has nothing to do with the use of UTC_TIMESTAMP.

>
> and the auto-update function may be problematic.
What auto-update function?

On 18 Mar, 13:16, Anees <muhd.an...@gmail.com> wrote:
> > No, UTC_TIMESTAMP() is a MySQL function, not a php function. I am
> > telling you to use this to insert the current time automatically.
>
> Can u Please give the sql query eg. to edit a field that using the
> same function
> when i tried
> ALTER TABLE `product` ADD `current_time` TIMESTAMP NOT NULL DEFAULT
> 'UTC_TIMESTAMP()'
>
> i got an error near the UTC_TIMESTAMP function

You would instead change the column definition to be a DATETIME value
and use this function on the insert statement.

Thanks very much Captain Paralytic and Peter H. Coffin
For ur Valuable help

On 18 Mar, 15:42, "Peter H. Coffin" <hell...@ninehells.com> wrote:
> On Tue, 18 Mar 2008 06:24:37 -0700 (PDT), Captain Paralytic wrote:
> > On 18 Mar, 12:40, "Peter H. Coffin" <hell...@ninehells.com> wrote:
> >> On Tue, 18 Mar 2008 04:11:34 -0700 (PDT), Captain Paralytic wrote:
> >> > On 18 Mar, 10:36, Anees <muhd.an...@gmail.com> wrote:
> >> >> > Store using UTC_TIMESTAMP and convert to local time when accessing.
>
> >> >> The field description given, when i created the table was
>
> >> >> cur_time timestamp CURRENT_TIMESTAMP
>
> >> >> So u r telling instead of inserting the current time automatically, i
> >> >> need to pass the value after calling UTC_TIMESTAMP from PHP?
>
> >> > No, UTC_TIMESTAMP() is a MySQL function, not a php function. I am
> >> > telling you to use this to insert the current time automatically.
>
> >> Unfortunately for Anees, this will not get exactly what is wanted, which
> >> is local time in Dubai.
> > This part out of context will not, but I believe that my actual
> > solution posted in my original reply will supply what was requested.
>
> >> It will, however, remove whatever local time
> >> bais exists. It does also complicate the coding somewhat
> > It will change the coding certainly. But I do not think it will become
> > particularly complex. One just needs to follow the correct syntax for
> > one's needs.
>
> >> in that the
> >> UTC_TIMESTAMP doesn't seem usable as a DEFAULT value at least on 5.0,
>
> >> create table `test`.`utc_test` (
> >> `rec_seq` int NOT NULL ,
> >> `val` varchar NULL ,
> >> `last_update` timestamp DEFAULT UTC_TIMESTAMP ,
> >> `created_at` timestamp DEFAULT UTC_TIMESTAMP ,
> >> PRIMARY KEY ( `rec_seq` )
> >> )
>
> >> Error Code : 1064
> >> You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL ,
> >> `last_update` timestamp DEFAULT UTC_TIMESTAMP ,
> >> `created_at' at line 3
> >> (0 ms taken)
> > This error refers to you having missed the field size from the
> > VARCHAR, it has nothing to do with the use of UTC_TIMESTAMP.
>
I forgot to post this link on my earlier post:
http://dev.mysql.com/doc/refman/5.0/...ate-table.html
It contains thisparagraph:
The DEFAULT clause specifies a default value for a column. With one
exception, the default value must be a constant; it cannot be a
function or an expression. This means, for example, that you cannot
set the default for a date column to be the value of a function such
as NOW() or CURRENT_DATE. The exception is that you can specify
CURRENT_TIMESTAMP as the default for a TIMESTAMP column.