Extract Day from Timestamp 1

[tcardoso]

Hi,

I have timestamp in the DB and I want to extract the day, month, etc directly from the timestamp...

Like it was SELECT DATAPART('day', `my_field_in_timestamp`) as day FROM `table`.

Any change in doing this with SQL only with a timestamp?

Thansk

 

[r937]

here, try running this and see what you get --

create table test_timestamps
( id integer not null primary key auto_increment
, foo varchar(99)
, mystamp timestamp
);

insert into test_timestamps (foo) values
 ('Curly'),('Larry'),('Moe');

insert into test_timestamps (foo,mystamp) values
 ('Shemp','1991-01-01')
,('Joe',  '1992-02-02')
,('Curly Joe','1993-03-03');

select id
     , mystamp
     , day(mystamp) as d
     , month(mystamp) as m
     , year(mystamp) as y
     , dayofweek(mystamp) as dw
     , dayofyear(mystamp) as dy
  from test_timestamps

r937.com | rudy.ca

 

[tcardoso]

Ok...

I see "my" error! The database isn't mine and the timestamp is in fact a int(20)

any chance of converting the int (that contains a timestamp) to a timestamp and choose the day all in the select?

Thanks

 

[r937]

select day(from_unixtime(mystamp)) as d
, month(from_unixtime(mystamp)) as m
, year(from_unixtime(mystamp)) as y
, ...


r937.com | rudy.ca

 

[tcardoso]

YES you spare me a lot of time doing coding in php and a lot of CPU time too.

O really love tek-tips forums Have always help me in every technology.

Will donate right now Thanks guys...