Hi,

I have a query re' the TIMESTAMP format.

I've set up identical tables on my DOS machine (Win.XP), and also on
my Linux box. Nothing fancy, just a few fields, two of which are
TIMESTAMP fields. These are set up as follows:

MYDATE1 TIMESTAMP NOT NULL,
MYDATE2 TIMESTAMP

Each table works fine on both the DOS box and the Linux box.

However, on the DOS box the dates are stored without hyphens, but on
the Linux box the dates are stored with hyphens.

eg DOS = 20070508112803
Linux= 2007-05-16 08:32:56

This is a problem because my scripts ***ume the first format, although
the probem is really that the formats are different - either one I can
live with, just so long as they are the same.

I'm guessing there must be some way to get MySQL to store the
TIMESTAMP field, and for that matter DATE fields, in a certain format,
but I have found nothing to help me so far.

Any information re' resolving this would be gratefully received.

Thanks!
Jane

EMAIL REMOVED wrote:

> eg DOS = 20070508112803
> Linux= 2007-05-16 08:32:56
>
> This is a problem because my scripts ***ume the first format, although
> the probem is really that the formats are different - either one I can
> live with, just so long as they are the same.

Use DATE_FORMAT, that way you will always be sure you get the dates in the
format you want.




--

//Aho

On 16 May 2007 08:50:02 -0700, EMAIL REMOVED wrote:
> Hi,
>
> I have a query re' the TIMESTAMP format.
>
> I've set up identical tables on my DOS machine (Win.XP), and also on
> my Linux box. Nothing fancy, just a few fields, two of which are
> TIMESTAMP fields. These are set up as follows:
>
> MYDATE1 TIMESTAMP NOT NULL,
> MYDATE2 TIMESTAMP
>
> Each table works fine on both the DOS box and the Linux box.
>
> However, on the DOS box the dates are stored without hyphens, but on
> the Linux box the dates are stored with hyphens.
>
> eg DOS = 20070508112803
> Linux= 2007-05-16 08:32:56
>
> This is a problem because my scripts ***ume the first format, although
> the probem is really that the formats are different - either one I can
> live with, just so long as they are the same.
>
> I'm guessing there must be some way to get MySQL to store the
> TIMESTAMP field, and for that matter DATE fields, in a certain format,
> but I have found nothing to help me so far.

You don't need to worry about how it's stored. MySQL will take DOS's
format[1] and there's an indication from a user note that adding '+0' to
the name of the column will have the timestamp read out without
delimiters[2].

[1] http://dev.mysql.com/doc/refman/5.0/en/datetime.html
[2] http://dev.mysql.com/doc/refman/5.0/...stamp-4-1.html

--
29. I will dress in bright and cheery colors, and so throw my enemies into
confusion.
--Peter Anspach's list of things to do as an Evil Overlord

Well, thanks for the reply, and I guess from your comment that
DATE_FORMAT would be used when reading from the table. However, there
must still be somewhere in the setup that tells MySQL how to save
dates, etc in certain formats, which is really what I would like to be
able to find out if possible.

Meantime I'll play with DATE_FORMAT and see how that works out.

Thanks!
Jane

Ah, just figured out that the var's I need are as follows:

mysql> SHOW VARIABLES LIKE "%format%";

.....
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| time_format | %H:%i:%s |
.....

Thanks,
Jane