Create a timestamp from separate date and time integers

Question

I'm trying to create a from - to datetime range from fields in a database table.

Unfortunately the fields aren't saved as a timestamp.

I have two entries

• time in hours from midnight
• date in days from 1 jan 1970

Is there a command to combine these entries so i can use a select statement and search in a range of time?

My database is sybase 15.7

Answer 1

I tried some options aswell, and this one seems to work for me:

CONVERT(
   VARCHAR,
   DATEADD(
      DAY,
      time_captured.end_date,
      `DEC 31 1971`
   ),
   104
) || `` || RIGHT(
   `00` || CONVERT(
      VARCHAR,
      FLOOR(time_captured.end_time/60)
   ),
   2
) || `:` || RIGHT(
   `00` + CONVERT(
      VARCHAR,
      time_captured.end_time%60
   ),
   2
) || `:00` >= CONVERT(
   VARCHAR,
   DATEADD(
      DAY,
      sp.start_date,
      `DEC 31 1971`
   ),
   104
) || `` || RIGHT(
   `00` || CONVERT(
      VARCHAR,
      FLOOR(sp.start_time/60)
   ),
   2
) || `:` || RIGHT(
   `00` + CONVERT(
      VARCHAR,
      sp.start_time%60
   ),
   2
) || `:00`

The way this database is made is quite weird. Thanks for all your answers!

Answer 2

timestamp column type in Sybase is not ... about time and date (look at this What is the mysterious 'timestamp' datatype in Sybase?).

If you want to be able to filter records by datetime (combining date and time) here is what you should look at :

1. a column of type datetime in your table

You could also use the getdate() function of sybase to have default value recorded, as of : datetime_field DATETIME default getDate()

2. using 2 columns of type DATE and TIME you can combien then to obtain a DATETIME type which you can use for filtering your records.

For example:

# Casting into a DATETIME from DATE and TIME fields
CAST(date_field AS DATETIME) + CAST(time_field AS DATETIME)

# Filtering against DATETIME
SELECT * FROM MyTable
WHERE datetime_field < CAST(date_field AS DATETIME) + CAST(time_field AS DATETIME)

Answer 3

If you want to make a manual conversion function that is translating some integer to datetime type you can write a UDF (user defined function) by yourself.

create function todatetime(@mydate int, @mytime int)
returns datetime
as
  declare @year int, @month int, @day, @hour int, @minute int, @second @int
  declare @datestring varchar(50)
  select @year = @mydate / 365 + 1970
  select @month = @mydate ...

  select @datestring = convert(varchar, @year + "-" + @month + ...
  return convert(datetime, @datestring)
go

Of course you have to add leap year support, etc., but is doable and it should do the job.

Answer 4

On the assumption that your named these two columns DaysSince1970 and HoursSinceMidnight ...

Then you can build an inferred datetime (not timestamp) using the expression

dateadd(hh,HoursSinceMidnight,dateadd(dd,DaysSince1970,'1-jan-1970'))

However, this a calculated column and is going to be terrible for indexed searches, so you will need some logic like.

DECLARE @DaysSince1970      int
,       @HoursSinceMidnight int
,       @SearchDateTime     datetime

SELECT  @DaysSince1970      = datediff(dd,'1-Jan-1970',@SearchDateTime)
,       @HoursSinceMidnight = datepart(hh,@SearchDateTime)

-- search your table
SELECT  *
FROM    tablename
WHERE   DaysSince1970       = @DaysSince1970
AND     HoursSinceMidnight  = @HoursSinceMidnight

Ideally your table will need and index on DaysSince1970, HoursSinceMidnight.

Finding rows in a range will be a simple extension of this, but remember that once you are checking across multiple days, don't check a range of 'raw' hours, just check the day range and then use the first method that I gave you to check the date/time range.