Storing Dates in DB

[Angelos]

What do you think is the best way to store Dates into a database ?
If you want to keep logs or buckups....

I am using date('dmYHis')
but I doesn't work really well ...
Is it better to use date(U) ?

Any suggestions ?

And if you can let me know how you can Display that date back in the Screen
!

Thanks

[Chris Hope]

Angelos wrote:

What do you think is the best way to store Dates into a database ?
If you want to keep logs or buckups....

I am using date('dmYHis')
but I doesn't work really well ...
Is it better to use date(U) ?
Any suggestions ?
Use the date or datetime field type - the exact name of the type depends
on your DBMS.
And if you can let me know how you can Display that date back in the
Screen !

Depending which DBMS you are using there may be a function for
formatting the date; in MySQL for example you would use the
date_format() function.

Otherwise you can use the PHP function strtotime() to change it to a
timestamp and then date() to format it as you wish. The downside with
this is you are limited to the range of a unix timestamp which is from
1970 to 2038.

--
Chris Hope | www.electrictoolbox.com | www.linuxcdmall.com

[Ray]

Hi

The best way that I found is to use
$date = date("Y-m-d H:i:s");

Works with mysql within a datetime field..
For a date field use date("Y-m-d") and for a time field use date("H:i:s")

Regards
Ray

"Angelos" <an*****@redcatmedia.net> wrote in message
news:d8**********@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...

What do you think is the best way to store Dates into a database ?
If you want to keep logs or buckups....

I am using date('dmYHis')
but I doesn't work really well ...
Is it better to use date(U) ?

Any suggestions ?

And if you can let me know how you can Display that date back in the
Screen !

Thanks

[Colin McKinnon]

Chris Hope wrote:

Angelos wrote:

What do you think is the best way to store Dates into a database ?
If you want to keep logs or buckups....

Use the date or datetime field type - the exact name of the type depends
on your DBMS.

Good advice.
Otherwise you can use the PHP function strtotime() to change it to a
timestamp and then date() to format it as you wish. The downside with
this is you are limited to the range of a unix timestamp which is from
1970 to 2038.

strtotime() is very american in its interpretation of dates. Also Unix
timestamps are a bad way to store dates - even if you are running on a 64
bit system (i.e. can work with timestamps after 2036, which isn't so far
away now) you can't work with timestamps before 1970.

HTH

C.

[Daniel Tryba]

Colin McKinnon <co**************@andthis.mms3.com> wrote:

strtotime() is very american in its interpretation of dates. Also Unix
timestamps are a bad way to store dates - even if you are running on a 64
bit system (i.e. can work with timestamps after 2036, which isn't so far
away now) you can't work with timestamps before 1970.

$ touch -t 191204140000 foo
$ ls -la foo
-rw-r--r-- 1 me me 0 Apr 14 1912 foo
$ stat foo
File: `foo'
Size: 0 Blocks: 0 IO Block: 4096 regular empty file
Device: eh/14d Inode: 20099 Links: 1
Access: (0644/-rw-r--r--) Uid: ( 491/ me) Gid: ( 491/ me)
Access: 1912-04-14 00:00:00.000000000 +0019
Modify: 1912-04-14 00:00:00.000000000 +0019
Change: 2005-06-16 16:19:20.000000000 +0200
$ stat -c %X foo
-1821399572
$ php4
<?php
echo date('r',-1821399572);
echo "\n";
?>
Sun, 14 Apr 1912 00:00:00 +0019
$

What do I have here.... a negative unix timestamp... IMHO on most systems
time_t is signed.

The biggest problem is that it is 32bit on most systems.

[Angelos]

> Use the date or datetime field type - the exact name of the type depends

on your DBMS.

And if you can let me know how you can Display that date back in the
Screen !

OK so I am using MySQL and I store it in a DATETIME datatype in the MySQL DB
in the folowing format :
$date = date("YmdHis");

So how you would display that ?
Is teh folowing correct ?
date('d M Y',strtotime(row['log_date']))

[Tony]

"Chris Hope" <bl*******@electrictoolbox.com> wrote in message
news:d8**********@lust.ihug.co.nz...

Angelos wrote:

What do you think is the best way to store Dates into a database ?
If you want to keep logs or buckups....

I am using date('dmYHis')
but I doesn't work really well ...
Is it better to use date(U) ?
Any suggestions ?

Use the date or datetime field type - the exact name of the type depends
on your DBMS.

And if you can let me know how you can Display that date back in the
Screen !

Depending which DBMS you are using there may be a function for
formatting the date; in MySQL for example you would use the
date_format() function.

Otherwise you can use the PHP function strtotime() to change it to a
timestamp and then date() to format it as you wish. The downside with
this is you are limited to the range of a unix timestamp which is from
1970 to 2038.

I don't recall date() being limited -

I often use something like $today = date('Y-m-d'); to get a date to enter
into a MySQL database. And when I have a date value that needs translating,
I just use string concatenation: $otherdate = $year . '-' . $month . '-' .
$day; (assuming $year, $month, and $day are numeric, of course)

[Chris Hope]

Tony wrote:

"Chris Hope" <bl*******@electrictoolbox.com> wrote in message
news:d8**********@lust.ihug.co.nz...

Angelos wrote:

What do you think is the best way to store Dates into a database ?
If you want to keep logs or buckups....

I am using date('dmYHis')
but I doesn't work really well ...
Is it better to use date(U) ?
Any suggestions ?

Use the date or datetime field type - the exact name of the type
depends on your DBMS.

And if you can let me know how you can Display that date back in the
Screen !

Depending which DBMS you are using there may be a function for
formatting the date; in MySQL for example you would use the
date_format() function.

Otherwise you can use the PHP function strtotime() to change it to a
timestamp and then date() to format it as you wish. The downside with
this is you are limited to the range of a unix timestamp which is
from 1970 to 2038.

I don't recall date() being limited -

I often use something like $today = date('Y-m-d'); to get a date to
enter into a MySQL database. And when I have a date value that needs
translating, I just use string concatenation: $otherdate = $year . '-'
. $month . '-' . $day; (assuming $year, $month, and $day are numeric,
of course)

date() isn't. strtotime() is as it returns a unix timestamp.

--
Chris Hope | www.electrictoolbox.com | www.linuxcdmall.com

[Chris Hope]

Colin McKinnon wrote:

Chris Hope wrote:

Angelos wrote:

What do you think is the best way to store Dates into a database ?
If you want to keep logs or buckups....

Use the date or datetime field type - the exact name of the type
depends on your DBMS.

Good advice.

Otherwise you can use the PHP function strtotime() to change it to a
timestamp and then date() to format it as you wish. The downside with
this is you are limited to the range of a unix timestamp which is
from 1970 to 2038.

strtotime() is very american in its interpretation of dates. Also Unix
timestamps are a bad way to store dates - even if you are running on a
64 bit system (i.e. can work with timestamps after 2036, which isn't
so far away now) you can't work with timestamps before 1970.

Hence my note about the downside :)

--
Chris Hope | www.electrictoolbox.com | www.linuxcdmall.com