Storing Dates in DB

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

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

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" <angelos@redcatmedia.net> wrote in message
news:d8rjp6$564$1@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
>

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.

Colin McKinnon <colin.deletethis@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.

> 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']))

"Chris Hope" <blackhole@electrictoolbox.com> wrote in message
news:d8rkje$7a7$1@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)

Tony wrote:

> "Chris Hope" <blackhole@electrictoolbox.com> wrote in message
> news:d8rkje$7a7$1@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

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