CODEWITHSUNDEEP
sqltimestamptype-conversionsybaseunix-timestamp
Zanam
Zanam

Reputation: 4807

In Sybase, Convert sql timestamp to unixtime

I'm using the Sybase database, How do I change SQL timestamp which appears in my database in form like: 2012-03-23 11:27:04.870000 to unixtime.

I want to be able to do something like this in sql: 

select time1, unix_time_function(time1) as time1_to_unixtime from mytable

Can it be done?

Upvotes: 1

Views: 10206

Answers (2)

Gopal Sanodiya
Gopal Sanodiya

Reputation: 106

There is the convert function in Sybase that has different options to convert time like

select convert(char(20),getdate(),101) --- value from 101 to 123

Check this article in the Sybase Infocenter.

Upvotes: 0

Bridge
Bridge

Reputation: 30651

According to Wikipedia, UNIX time is the number of seconds elapsed since 1st Jan 1970 (not including leap seconds).

Bearing that in mind (and if I haven't completely lost it) it should just be the difference between 1970-01-01 and your time, in seconds:

SELECT time1,
       Datediff(SECOND, '1970-01-01', time1) AS time1_to_unixtime
FROM   mytable

I'm not sure whether DateDiff takes leap seconds into account, if not, there might be some adjustments needed.

Documentation for DateDiff in Sybase is here.

Upvotes: 3

Related Questions