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]

Re: Storing Dates in DB

Angelos wrote:
[color=blue]
> 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 ?[/color]

Use the date or datetime field type - the exact name of the type depends
on your DBMS.
[color=blue]
> And if you can let me know how you can Display that date back in the
> Screen ![/color]

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]

Re: Storing Dates in DB

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@redcat media.net> wrote in message
news:d8rjp6$564 $1@nwrdmz02.dmz .ncs.ea.ibs-infra.bt.com...[color=blue]
> 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
>[/color]

[Colin McKinnon]

Re: Storing Dates in DB

Chris Hope wrote:
[color=blue]
> Angelos wrote:
>[color=green]
>> What do you think is the best way to store Dates into a database ?
>> If you want to keep logs or buckups....[/color]
>
>
> Use the date or datetime field type - the exact name of the type depends
> on your DBMS.
>[/color]
Good advice.
[color=blue]
> 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.
>[/color]

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]

Re: Storing Dates in DB

Colin McKinnon <colin.deleteth is@andthis.mms3 .com> wrote:[color=blue]
> 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.[/color]

$ 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.000000 000 +0019
Modify: 1912-04-14 00:00:00.000000 000 +0019
Change: 2005-06-16 16:19:20.000000 000 +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]

Re: Storing Dates in DB

> Use the date or datetime field type - the exact name of the type depends[color=blue]
> on your DBMS.
>[color=green]
>> And if you can let me know how you can Display that date back in the
>> Screen ![/color][/color]

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(ro w['log_date']))

[Tony]

Re: Storing Dates in DB

"Chris Hope" <blackhole@elec trictoolbox.com > wrote in message
news:d8rkje$7a7 $1@lust.ihug.co .nz...[color=blue]
> Angelos wrote:
>[color=green]
>> 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 ?[/color]
>
> Use the date or datetime field type - the exact name of the type depends
> on your DBMS.
>[color=green]
>> And if you can let me know how you can Display that date back in the
>> Screen ![/color]
>
> 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.[/color]

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]

Re: Storing Dates in DB

Tony wrote:
[color=blue]
> "Chris Hope" <blackhole@elec trictoolbox.com > wrote in message
> news:d8rkje$7a7 $1@lust.ihug.co .nz...[color=green]
>> Angelos wrote:
>>[color=darkred]
>>> 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 ?[/color]
>>
>> Use the date or datetime field type - the exact name of the type
>> depends on your DBMS.
>>[color=darkred]
>>> And if you can let me know how you can Display that date back in the
>>> Screen ![/color]
>>
>> 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.[/color]
>
> 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)[/color]

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

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

[Chris Hope]

Re: Storing Dates in DB

Colin McKinnon wrote:
[color=blue]
> Chris Hope wrote:
>[color=green]
>> Angelos wrote:
>>[color=darkred]
>>> What do you think is the best way to store Dates into a database ?
>>> If you want to keep logs or buckups....[/color]
>>
>>
>> Use the date or datetime field type - the exact name of the type
>> depends on your DBMS.
>>[/color]
> Good advice.
>[color=green]
>> 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.
>>[/color]
>
> 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.[/color]

Hence my note about the downside :)

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